none
"The wait operation timed out" error! RRS feed

  • Question

  • Hi All.

    I have a LINQ statements that when I execute it, occur error:

    I have 2 query that union them and execute then.

       var db = PayanehHamta.BL.GlobalFunction.AvarezDb;
                var enter = (from c in db.Cities
                            join e in db.EnterGateSoorats on c.cityID equals e.startCityID
                            where start<= e.enterDate && e.enterDate<= end
                            group e by new
                            {
                                e.PayanehId,
                                e.Bus.busTypeID,
                                c.IsInProvince
                            } into g
                            select g)
                         //   .ToList()
                            ;
    
    
                var BusEnter = from g in enter
                               select new
                               {
                                   Sum = (int?)g.Count(),
                                   SumPassenger = g.Sum(s => (s.passengerNumber)),
                                   g.Key.PayanehId,
                                   g.Key.busTypeID,
                                   g.Key.IsInProvince,
                                   Col = 1,
                               };
    
                      var Exit = (from s in db.SooratVaziats
                           join sv in db.Services on s.serviceID equals sv.serviceID
                           join c in db.Cities on sv.destinationCityID equals c.cityID
                           join d in db.Destinations on s.sooratVaziatID equals d.SooratVaziatId
                           where start<= s.saveDate && s.saveDate<= end && !s.deleted  
                           group s by new
                           {
                               payanehId = s.payanehId.HasValue ? s.payanehId.Value : 1,
                               sv.Bus.busTypeID,
                               c.IsInProvince
                           } into g
                            select g)
                           //.ToList()
                            ;
    
                var busExit = from g in Exit
                              select new
                              {
                                  Sum = g.Where(s => !s.Taavoni.isSavarStation).Sum(d => d.numberOfSV),
                                  SumPassenger = (int?)g.Sum(d => d.Destinations.Sum(r => r.PassengerCount)),
                                  PayanehId = g.Key.payanehId,
                                  g.Key.busTypeID,
                                  g.Key.IsInProvince,
                                  Col = 2,
                              };
    
             
                var dt = BusEnter.Union(busExit);
    
                var dd1 =dt.ToList().Select(d =>
                    new
                    {
                        d.Sum,
                        d.SumPassenger,
                        d.PayanehId,
                        busTypeID = d.busTypeID > 2 ? 2 : d.busTypeID,
                        d.IsInProvince,
                        d.Col,
                    });

    When I get generated query from SQL Profiler and execute in SQL Management, it takes about 25 seconds.But when run this code, above error has occurred.  I increase connection timeout in connection string, increase remote connection timeout in SQL Server, increase web request time out in web.config and..., But....

    Also when I execute first query by add .ToList() to end of it, this error has occurred. But generated SQL statements (copied from Profiler) execute in 2 seconds in SQL Management.

    Please help me to solve it. I run it in .NET 4.0 and ASP.NET, that use SQL Server 2008 R2.

    Thanks.


    Pooneh.


    • Edited by AliReza Pooneh Saturday, April 7, 2012 5:56 AM Put better image on post.
    Saturday, April 7, 2012 5:50 AM

Answers

  • Hi AliReza Pooneh,

    In Entity Framework, before executing the generated T-SQL in the database, there are lots of preparing things need to do. 

    When you executing a Linq statement, Entity Framework will do the actions as below.

    1. Loading metadata.

    2. Generate view.

    3. Parameter evaluation.

    4. Query Translation.

    5. Materializer generation.

    6. Database query execution.

    7. Object materialization.

    Executing T-SQL is only one of these actions, so it certainly will take more time.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, April 11, 2012 5:11 AM
    Moderator
  • I'd suggest you to make all database job in SQL. All your joins and unions, That will give you the power over controlling the select and union process, you'll make sure that if something goes wrong, it's not about application.

    When you get a complete result in database, bring it to the application using LINQ.

    All these steps will reduce your connections to the database and all complex job will be transferred to SQL instead of LINQ.

    Also it will work much more faster


    Please mark as reply if helped.
    Also visit my blog http://msguy.net/

    Thursday, April 12, 2012 7:38 AM

All replies

  • Hi AliReza Pooneh,

    Welcome to MSDN Forum.

    Have you tried to set command timeout? It may solve the issue.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, April 9, 2012 3:10 AM
    Moderator
  • Hi AliReza Pooneh,

    Have you solved the issue? I look forward to hearing from you.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, April 11, 2012 3:09 AM
    Moderator
  • Hi and thanks for your support.

    I tried another solutions  like SqlCommand by text type and StoredProc type with long timeout, query with Entity framework and call stored procedure with EF and .... but didn't work!

    Last, I write my query in 3 stored procedure and call them separate in .net by LINQ, union them in my application and use result. It execute by better answer time and performance.

    But I confused for that problem, why generated query executed in SSMS in less time, but Linq and other ways can't execute it in long time?! why?


    Pooneh.

    Wednesday, April 11, 2012 4:55 AM
  • Hi AliReza Pooneh,

    In Entity Framework, before executing the generated T-SQL in the database, there are lots of preparing things need to do. 

    When you executing a Linq statement, Entity Framework will do the actions as below.

    1. Loading metadata.

    2. Generate view.

    3. Parameter evaluation.

    4. Query Translation.

    5. Materializer generation.

    6. Database query execution.

    7. Object materialization.

    Executing T-SQL is only one of these actions, so it certainly will take more time.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, April 11, 2012 5:11 AM
    Moderator
  • I'd suggest you to make all database job in SQL. All your joins and unions, That will give you the power over controlling the select and union process, you'll make sure that if something goes wrong, it's not about application.

    When you get a complete result in database, bring it to the application using LINQ.

    All these steps will reduce your connections to the database and all complex job will be transferred to SQL instead of LINQ.

    Also it will work much more faster


    Please mark as reply if helped.
    Also visit my blog http://msguy.net/

    Thursday, April 12, 2012 7:38 AM