none
What is the way to use EF? RRS feed

  • Question

  • Hi,

    I am using windows 7, VS2010 and doing a WCF project. I found writing Linq query is very difficult for me, though I am good at TSQL, paricularly if the query involved in many conditions. Like user want to select products by name or by brands or by price etc. your queries need to be different. Use linq, this would be very complicated. So what I do with EF is that I use ado.net for select query(easy), and use EF for update, insert rows. Is this a common way to do with EF? Or I should use EF for select as well?

    Monday, November 21, 2011 4:57 AM

Answers

  • > I found writing Linq query is very difficult for me, though I am good at TSQL, paricularly if the query involved in many conditions. [...] Is this a common way to do with EF? Or I should use EF for select as well?

     
    try using the following ...
    var d = new Data();   // Data : DbContext
    d.Database.ExecuteSqlCommand(...
    d.Database.SqlQuery(...
    
    
    • Marked as answer by peter 9 Friday, November 25, 2011 3:41 AM
    Monday, November 21, 2011 5:06 AM
  • Hi,

    No, that is not common and you will loose a great deal of functionality by doing this. I recommend you to use EF all the time for get full use of the power of EF. If you can't do this, I would rather recommend you to use classic ADO.NET with strong typed DataSet/DataTable as this would make your day easier.

    Writing LINQ is a bit different than you are used to when writing queries agaisnt database, but this is more a learing step and when you are used to it it is as simple as SQL. In addition you can use the same query language against other sources like List<T> or XML.

    If we take your example where a user want to select product by name or brands or price you could write something like this

    public enum OrderByProductColumns
    {
       None = 0,
       Name = 1,
       Brand = 2,
       Price = 3
    }
    
    public List<Product> GetProducts(OrderByProductColumns orderBy)
    {
       using(MyContext context = new MyContext())
       {
          var query = context.Products.Where(....); //.... is your filter
    
          switch(orderBy)
          {
             case Name:
                query = query.OrderBy(row => row.Name);
                break;
             case Brand:
                query = query.OrderBy(row => row.Brand);
                break;
             case Price:
                query = query.OrderBy(row => row.Price);
                break;
          }
          return query.ToList();
       }
    }
    

    This is using LINQ Methods which I find a bit more flexible than LINQ Query but that is a matter of taste.

    I hope this helps!

     


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    • Marked as answer by peter 9 Friday, November 25, 2011 3:41 AM
    Monday, November 21, 2011 7:00 AM
  • Hi peter,

    Firstly, I agree with @Rune, but for the complex CommandText, I think you should follow @Malobukv way or use Stored procedure in EF here: http://msdn.microsoft.com/en-us/library/bb896279.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by peter 9 Friday, November 25, 2011 3:41 AM
    Tuesday, November 22, 2011 6:55 AM
    Moderator
  • On 11/24/2011 9:24 PM, peter 9 wrote:
    > Hi,
    >
    > Thanks al lot for all of you.
    >
    > One more question, what is the difference between ado.net way and the
    > way Malobukv suggested i.e.
    >
    > var d = new Data(); // Data : DbContext
    > d.Database.ExecuteSqlCommand(...
    > d.Database.SqlQuery(...
    >
    > Or I mean what is the benefit of the later way?
    >
    >
    >
     
    • Marked as answer by peter 9 Friday, November 25, 2011 3:42 AM
    Friday, November 25, 2011 2:28 AM

All replies

  • > I found writing Linq query is very difficult for me, though I am good at TSQL, paricularly if the query involved in many conditions. [...] Is this a common way to do with EF? Or I should use EF for select as well?

     
    try using the following ...
    var d = new Data();   // Data : DbContext
    d.Database.ExecuteSqlCommand(...
    d.Database.SqlQuery(...
    
    
    • Marked as answer by peter 9 Friday, November 25, 2011 3:41 AM
    Monday, November 21, 2011 5:06 AM
  • Hi,

    No, that is not common and you will loose a great deal of functionality by doing this. I recommend you to use EF all the time for get full use of the power of EF. If you can't do this, I would rather recommend you to use classic ADO.NET with strong typed DataSet/DataTable as this would make your day easier.

    Writing LINQ is a bit different than you are used to when writing queries agaisnt database, but this is more a learing step and when you are used to it it is as simple as SQL. In addition you can use the same query language against other sources like List<T> or XML.

    If we take your example where a user want to select product by name or brands or price you could write something like this

    public enum OrderByProductColumns
    {
       None = 0,
       Name = 1,
       Brand = 2,
       Price = 3
    }
    
    public List<Product> GetProducts(OrderByProductColumns orderBy)
    {
       using(MyContext context = new MyContext())
       {
          var query = context.Products.Where(....); //.... is your filter
    
          switch(orderBy)
          {
             case Name:
                query = query.OrderBy(row => row.Name);
                break;
             case Brand:
                query = query.OrderBy(row => row.Brand);
                break;
             case Price:
                query = query.OrderBy(row => row.Price);
                break;
          }
          return query.ToList();
       }
    }
    

    This is using LINQ Methods which I find a bit more flexible than LINQ Query but that is a matter of taste.

    I hope this helps!

     


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    • Marked as answer by peter 9 Friday, November 25, 2011 3:41 AM
    Monday, November 21, 2011 7:00 AM
  • Hi,

    Thank you very much for your help.

    I copy my simplified sql below. This is the first sql I felt I couldn't do it with EF. The features of the sql and/or the other sql are

    1) select in select, and the select may contains parameters

    2) left outer join in from. may have more than one left outer join in from

    3) conditions in select, from and where clauses

    4) select in where

    The reason I use ado.net is that sql statement is string, I can easily write it and pass it into the ado object.

    But with EF, I think (maybe wrong) I have to declare many  var query and make the code very cumbersome.

    The reason I copy my sql here is that I wish you assess whether it can be done by EF because I am an newbie in EF, I have no much ideas on it.

                cmd = "select patSurname, patOthername," +
                    "(select count(*) from srvhead where svhAccid = a1.acbAccId) SrvCnt," +
                    "(select count(*)from appt where apppatid = patPatid and AppDate = '" + SrvGlobalFuncs.SystemDate.ToString("yyyy/MM/dd") + "') todayAppt," +
                    "(Select hisHistory from HistoryFile where hisAccid = acbAccid and hisCategory1 = 5) hisHistory";
                if (selBy == "appointments")
                {
                    cmd = cmd + ", appDate";
                    cmd = cmd + "\r\n" + "from ((Appt left outer join Patient p on appPatid = patPatId) left outer join accbase a1 on acbPatId = patPatId and acbSiteId = " + siteId.ToString() + ") left outer join sitegroup sg on sgrSiteId = acbSiteID";
                }
                else
                        cmd = cmd + "\r\n" + "from (Patient p left outer join accbase a1 on acbPatId = patPatId and acbSiteId = " + siteId.ToString() + ") left outer join sitegroup sg on sgrSiteId = acbSiteID";


                if (selBy.ToLower() == "name")
                {

                    if ((Surname != null) && (OtherName != null))
                        wClause = @" where ((patSurname like '" + Surname.Replace("'", "''") + "%' and patOthername like '" + OtherName.Replace("'", "''") + "%')" +
                                  @" or patAlias like '" + Alias.Replace("'", "''") + "%')";
                    else
                        if (Surname != null)
                        wClause = @" where (patSurname like '" + Surname.Replace("'", "''") + "%'" +
                                    @" or patAlias like '" + Alias.Replace("'", "''") + "%')";
                    else
                    if (OtherName != null)
                        wClause = @" where (patOtherName like '" + OtherName.Replace("'", "''") + "%'" +
                                    @" or patAlias like '" + Alias.Replace("'", "''") + "%')";
                }
                else
                if (selBy.ToLower() == "appointments")
                {
                    wClause = @" where appdate = '" + searchStr + "'" +
                                " and patPatId = appPatId";
                    if (DrId > 0)
                        wClause = wClause + @" and (appDrId = " + DrId.ToString() + " or appDrId is null)";

                }
                wClause = wClause + "  and patPatId > 0";

                if (selBy.ToLower() != "appointments")
                    cmd = cmd + wClause + " order by patSurname, patOthername, patPatId";
                else
                    cmd = cmd + wClause + " ORDER BY AppTime, appBook, appPatID";

     

     

    Monday, November 21, 2011 11:29 PM
  • Hi peter,

    Firstly, I agree with @Rune, but for the complex CommandText, I think you should follow @Malobukv way or use Stored procedure in EF here: http://msdn.microsoft.com/en-us/library/bb896279.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by peter 9 Friday, November 25, 2011 3:41 AM
    Tuesday, November 22, 2011 6:55 AM
    Moderator
  • Hi again!

    Well, that wasn't an easy query. However it possible to implement in EF, and no you don't need to define many var query objects. However without knowing your model it's a bit problematic to tell you how to implement this. It even may be to complicated to explain the whole SQL even then.

    I would recommend you to read about how LINQ and EF works together and take some time experimenting with that before you dive into an SQL like this and try to convert that.

    A another solution is that you create a stored procedure that handles your SELECT and uses that instead. It may be better for performance too.

    A final word about your existing code. Based on how your filitering parameters is passed (userbased or system based) that code is vunerable for SQL injection.

     


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    Tuesday, November 22, 2011 7:13 AM
  • Hi,

    Thank u.

    All of you don't like use ado sql way. I will have to learn more about LINQ and EF.

    Wednesday, November 23, 2011 4:58 AM
  • Hi peter,

    All the LINQ operations will be translate to "T-SQL" by EF Provider and run on ADO.NET2.0.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, November 23, 2011 9:12 AM
    Moderator
  • On 11/22/2011 11:58 PM, peter 9 wrote:
    > Hi,
    >
    > Thank u.
    >
    > All of you don't like use ado sql way. I will have to learn more about
    > LINQ and EF.
    >
     
    You don't have to use Linq to do queries in EF.
     
     
     
    Wednesday, November 23, 2011 3:10 PM
  • Hi,

    Thanks al lot for all of you.

    One more question, what is the difference between ado.net way and the way Malobukv suggested i.e.

    var d = new Data();   // Data : DbContext
    d.Database.ExecuteSqlCommand(...
    d.Database.SqlQuery(...

    Or I mean what is the benefit of the later way?



    Friday, November 25, 2011 2:24 AM
  • On 11/24/2011 9:24 PM, peter 9 wrote:
    > Hi,
    >
    > Thanks al lot for all of you.
    >
    > One more question, what is the difference between ado.net way and the
    > way Malobukv suggested i.e.
    >
    > var d = new Data(); // Data : DbContext
    > d.Database.ExecuteSqlCommand(...
    > d.Database.SqlQuery(...
    >
    > Or I mean what is the benefit of the later way?
    >
    >
    >
     
    • Marked as answer by peter 9 Friday, November 25, 2011 3:42 AM
    Friday, November 25, 2011 2:28 AM