none
Lingtoentities query on using foreach is slow RRS feed

  • Question

  • hi frnds,

                   I am using linqtoentities query..,my query is as below

    var favourdata = (from u in obj2.psms_voters_master
                        where pids.Contains(u.parliament_id) && (u.voterflag == "0")
                        orderby u.pollingcenter_id, u.favour
                        group u by new { u.pollingcenter_id } into favourcount
                        select new
                        {
                            pollingcenter_id = favourcount.Key.pollingcenter_id,
                            favour = favourcount.Count(rg => rg.favour == "0"),
                            fiftyfifty = favourcount.Count(rg => rg.favour == "1"),
                            negative = favourcount.Count(rg => rg.favour == "2"),
                            nottraceable = favourcount.Count(rg => rg.favour == "4"),
                            notyetcontacted = favourcount.Count(rg => rg.favour == "5"),
                            voterscount = favourcount.Count()
                        });

    when i use breakpoint from this line query its comes fast..but when i use it in foreach loop as below

     foreach (var x in favourdata)
                        {
                          

                        }

                 at the foreach line its taking more time to go inside the loop, once its get into the loop its all fast....

    Pls help me out from this issue...,how to make the forech loop fast..

    Thanks & regards,

    M.Ramakrishna.

    Monday, April 23, 2012 4:16 AM

Answers

  • Hi krishna2011,

    The problem is that your Func<> signature doesn't match the query you're passing to the Compile() method. The query is returning a collection of an anonymous type and you've defined the return type of the Func as IQueryable<psmsModel.psms_voters_master>. Since you can't define a delegate which returns an anonymous type you'll need to create a class that matches the results of your query like so:

    public class MyQueryResult
    {
        public int pollingcenter_id { get; set; }
        public int favour { get; set; }
        public int fiftyfifty { get; set; }
        public int negative { get; set; }
        public int nottraceable { get; set; }
        public int notyetcontacted { get; set; }
        public int voterscount { get; set; }
    }

    You'd then project your query into this class rather than an anonymous type:

    static readonly Func<psmsModel.psmsEntities2, Int64[], string, IQueryable<MyQueryResult>> s_compiledQuery2 = 
    CompiledQuery.Compile<psmsModel.psmsEntities2, Int64[], string, IQueryable<MyQueryResult>>(
    (...) => from u in ...
             etc...
             select new MyQueryResult
             {
                pollingcenter_id = favourcount.Key.pollingcenter_id,
                favour = favourcount.Count(rg => rg.favour == "0"),
                etc...
             });

    Regards,

    Tyler

    • Marked as answer by krishna2011 Tuesday, April 24, 2012 6:48 AM
    Tuesday, April 24, 2012 1:24 AM

All replies

  • In the line of var favourdata = ...; the query doesn't execute, it only define a query variable, so it is very fast. When you execute the foreach statement, the query will execute and load the result into memory, so it will take some time to hit database and query out the results. After that, you are looping elements in a collection, so it is very fast. 

    It is deffer loading.


    Fighting like Allen Iverson. Neve give up!

    Monday, April 23, 2012 7:33 AM
  • hi Hero,

              Thanks for the reply..,but how can i make it fast at the time of foreach....pls help me out ASAP.

    Thanks & Regards,

    M.Ramakrishna.

    Monday, April 23, 2012 8:08 AM
  • hi,

    may be the deferred execution be a problem.

    !. u can use the following code to disable deferred loading

    db.DeferredLoadingEnabled = false;

    2. second option is u can use the ToList() as in the following code

    var favourdata = (from u in obj2.psms_voters_master
                        where pids.Contains(u.parliament_id) && (u.voterflag == "0")
                        orderby u.pollingcenter_id, u.favour
                        group u by new { u.pollingcenter_id } into favourcount
                        select new
                        {
                            pollingcenter_id = favourcount.Key.pollingcenter_id,
                            favour = favourcount.Count(rg => rg.favour == "0"),
                            fiftyfifty = favourcount.Count(rg => rg.favour == "1"),
                            negative = favourcount.Count(rg => rg.favour == "2"),
                            nottraceable = favourcount.Count(rg => rg.favour == "4"),
                            notyetcontacted = favourcount.Count(rg => rg.favour == "5"),
                            voterscount = favourcount.Count()
                        }).ToList();

    regards

    Jagan

    Monday, April 23, 2012 9:09 AM
  • hi jagan,

                    thanks for the reply, but wats db...,as per my code db means obj2?

    Monday, April 23, 2012 9:31 AM
  • Hi krishna2011,

    Check out this link for several things to take into account regarding performance when using Entity Framework: http://msdn.microsoft.com/en-us/library/cc853327.aspx I think that you'll most likely benefit from using Compiled Queries and possibly by pre-generating the views. 

    @Chidambara - The DeferredLoadingEnabled property is for LINQ to SQL's DataContext. Assuming you're using an ObjectContext for your LINQ to Entities query you'd use obj2.ContextOptions.LazyLoadingEnabled. Based on the query this will do nothing though, since you're not instantiating any entities so there are no Navigation Properties to lazy load.

    Regards,

    Tyler

    Monday, April 23, 2012 9:49 AM
  • Hi tyler,

                 Thanks for the reply, i have tried using complied query as follows

      var favourdata1 =  CompiledQuery.Compile((psmsModel.psmsEntities2 ctx) =>
                        from u in ctx.psms_voters_master
                        where pids.Contains(u.parliament_id) && (u.voterflag == "0")
                        orderby u.pollingcenter_id, u.favour
                        group u by new { u.pollingcenter_id } into favourcount
                        select new
                        {
                            pollingcenter_id = favourcount.Key.pollingcenter_id,
                            favour = favourcount.Count(rg => rg.favour == "0"),
                            fiftyfifty = favourcount.Count(rg => rg.favour == "1"),
                            negative = favourcount.Count(rg => rg.favour == "2"),
                            nottraceable = favourcount.Count(rg => rg.favour == "4"),
                            notyetcontacted = favourcount.Count(rg => rg.favour == "5"),
                            voterscount = favourcount.Count()
                        });

    psmsModel.psmsEntities2 kajianobj = new psmsModel.psmsEntities2();
          
    var favourdata = favourdata1.Invoke(kajianobj);

     foreach (var x in favourdata)
                        {
                          

                        }

    But still at the time of foreach its slow........So pls help me out ASAP.

    Thanks & Regards,

    M.Ramakrishna.

    Monday, April 23, 2012 10:41 AM
  • hi Krishna,

    is there any increase in performance when removing all the count part of the Query

    regards

    Jagan

    Monday, April 23, 2012 11:05 AM
  • hi jagan,

                   Sorry jagan, i cant remove the count from the query...,my output is based on query itself.

    Thanks & regards,

    Krishna.

    Monday, April 23, 2012 11:38 AM
  • Hi Krishna, 

    The delegate returned from the CompiledQuery.Compile() method needs to be stored in a static variable to assure that it isn't re-compiled. With the example you've given it's a locally scoped variable and so it will be re-compiled every time it is called. Note that in order to store it as a static delegate you'll need to return a defined type rather than an anonymous type. See the examples here: http://msdn.microsoft.com/en-us/library/bb896297.aspx where the Func<> variables are stored as static read-only.

    Regards,

    Tyler

    Monday, April 23, 2012 1:17 PM
  • hi tyler,

                Thanks for the response, as u have suggested me to use static readonly...i used like this in my query, can u please help me 2 sort out this error....

    Monday, April 23, 2012 2:17 PM
  • Hi krishna2011,

    The problem is that your Func<> signature doesn't match the query you're passing to the Compile() method. The query is returning a collection of an anonymous type and you've defined the return type of the Func as IQueryable<psmsModel.psms_voters_master>. Since you can't define a delegate which returns an anonymous type you'll need to create a class that matches the results of your query like so:

    public class MyQueryResult
    {
        public int pollingcenter_id { get; set; }
        public int favour { get; set; }
        public int fiftyfifty { get; set; }
        public int negative { get; set; }
        public int nottraceable { get; set; }
        public int notyetcontacted { get; set; }
        public int voterscount { get; set; }
    }

    You'd then project your query into this class rather than an anonymous type:

    static readonly Func<psmsModel.psmsEntities2, Int64[], string, IQueryable<MyQueryResult>> s_compiledQuery2 = 
    CompiledQuery.Compile<psmsModel.psmsEntities2, Int64[], string, IQueryable<MyQueryResult>>(
    (...) => from u in ...
             etc...
             select new MyQueryResult
             {
                pollingcenter_id = favourcount.Key.pollingcenter_id,
                favour = favourcount.Count(rg => rg.favour == "0"),
                etc...
             });

    Regards,

    Tyler

    • Marked as answer by krishna2011 Tuesday, April 24, 2012 6:48 AM
    Tuesday, April 24, 2012 1:24 AM
  • hi Tyler,

               Thanks for the reply..., as u have said before i used the same way...now its free of error in code wise...  so i called it in my click event... as folllows

    psmsmodel.psmsentities2 obj2=new psmsmodel.psmsentities2();

       IQueryable<MyQueryResult> favourdata = kajianquery.Invoke(obj2, pids, "0");

    foreach( var x in favourdata)

    {

    }

    at foreach line execution i am getting an error as

    The specified parameter 'pidvalues' of type 'System.Collections.Generic.List`1[[System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' is not valid. Only scalar parameters (such as Int32, Decimal, and Guid) are supported.

    Pls help me out from this error ASAP.

    Thanks & regards,

    Krishna.

    Tuesday, April 24, 2012 6:48 AM