Entity Framework SQL statement

Question

I want to query the table News from the database called StiriDB. There I want take all the entries that have in the Description field the word Stored in SearchTxt. I can't quite figure out what the SqlQuery method wants from me ... This is the code:

public IQueryable<News> GetProducts()
{
    var _db = new SiteStiri.Models.NewsContext();
    String SearchTxt = Convert.ToString(Request.QueryString["Cauta"]);
    String queryTxt = "Select * from StiriDB.News where Description like '%" + SearchTxt + "%'";
    IQueryable<News> query = _db.News.SqlQuery<News>(queryTxt);

    if ("DesDate".Equals(DropDownSelect.SelectedItem.Value))
    {
        query = query.OrderByDescending(u => u.ReleaseDate);
    }

    if ("AsDate".Equals(DropDownSelect.SelectedItem.Value))
    {
        query = query.OrderBy(u => u.ReleaseDate);
    }

    if ("AsAlp".Equals(DropDownSelect.SelectedItem.Value))
    {
        query = query.OrderBy(u => u.NewsTitle);
    }

    if ("DesApl".Equals(DropDownSelect.SelectedItem.Value))
    {
        query = query.OrderByDescending(u => u.NewsTitle);
    }

    return query;
}

Additional details : GetProducts is called by a ListView. SearchTxt is taken with QueryString of Request because it's an URL attribute. The many ifs are for sorting the data in ascending and descending order based on certain criteria (the ifs work, I just need the SqlQuery to work as intended);

No!!! DON'T create SQL query strings if you're using Linq-to-EF; you're losing all the benefits (compiled queries + parametric queries that prevent SQL injection). Use Linq syntax!! — frenchie
– frenchie, Commented Oct 25, 2014 at 11:10
Oh i don't really know , i am a newbie. And i am learning by myself so bits and pieces from here to there :D — Lucian Tarna
– Lucian Tarna, Commented Oct 25, 2014 at 11:20
No worries; go with Paul's approach: var query = from n in ctx.News.... and not queryTxt = "Select * from StiriDB.N..." — frenchie
– frenchie, Commented Oct 25, 2014 at 11:26
A small suggestion for SQL queries, if you would need to create SQL queries in the furture, try to use parameterized queries, it's much saver. — User2012384
– User2012384, Commented Oct 25, 2014 at 15:27

BLoB · Accepted Answer · 2014-10-25 11:12:58Z

3

Use Linq to Entities query, something like...

public IQueryable<News> GetProducts()
{
    var ctx = new SiteStiri.Models.NewsContext();

    var query = from n in ctx.News
                where n.Description.Contains(SearchTxt)
                select n;

    if ("DesDate".Equals(DropDownSelect.SelectedItem.Value))
    {
        query = query.OrderByDescending(u => u.ReleaseDate);
    }
    else if ("AsDate".Equals(DropDownSelect.SelectedItem.Value))
    {
        query = query.OrderBy(u => u.ReleaseDate);
    }
    else if ("AsAlp".Equals(DropDownSelect.SelectedItem.Value))
    {
        query = query.OrderBy(u => u.NewsTitle);
    }
    else if ("DesApl".Equals(DropDownSelect.SelectedItem.Value))
    {
        query = query.OrderByDescending(u => u.NewsTitle);
    }

    return query;
}

answered Oct 25, 2014 at 11:12

BLoB

9,7879 gold badges58 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework SQL statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related