none
is it possible to do eager loding when I use raw SQL with SQLQuery? RRS feed

  • Question

  • I am using EF 4.4 (EF 5.0 with target .NET 4.0) and I know that I can do a eager loading with the Include, but this use Linq and is less efficient that raw SQL.

    I would like to know if there is any way to use eager loading  when I use raw SQL.

    Thanks.

    Tuesday, June 18, 2013 10:41 AM

Answers

  • You should keep the number of roundtrips to the database as low as possible. If a single inner join is slow, your should look into indexes in SQL Server or whatever RDBMS you are using. For complex select statements, the best thing would probably be to use a stored procedure.
    • Marked as answer by ComptonAlvaro Tuesday, June 18, 2013 2:29 PM
    Tuesday, June 18, 2013 12:40 PM

All replies

  • No, I don't think it's impossible to load related objects through raw SQL. You'll have to use eager loading, explicit loading, lazy loading or joins.
    Tuesday, June 18, 2013 10:56 AM
  • Then perhaps one solution is to use one additional query for each principal registers found. Somethink like that:

    using (catalogoEntities miContexto = new catalogoEntities())
                {
                    lstResultado = miContexto.Videos.SqlQuery(strConsultaTest).ToList<Videos>();
    
                    foreach (Videos iteradorVideo in lstResultado)
                    {
                        iteradorVideo.Generos = miContexto.Generos.SqlQuery("select * from Generos, VideosGeneros where Generos.IDGenero = VideosGeneros.IDGenero and VideosGeneros.IDVideo = " + iteradorVideo.IDVideo).ToList<Generos>();
                    }
                }

    This make more coonections to the database, but in time I have done tests and is faster than the use of include in a linq to sql query.

    Perhaps this many additional connections can be a problem, but if the result is faster, perhaps the need to make more connections to the database no matter so much.

    Tuesday, June 18, 2013 11:26 AM
  • You should keep the number of roundtrips to the database as low as possible. If a single inner join is slow, your should look into indexes in SQL Server or whatever RDBMS you are using. For complex select statements, the best thing would probably be to use a stored procedure.
    • Marked as answer by ComptonAlvaro Tuesday, June 18, 2013 2:29 PM
    Tuesday, June 18, 2013 12:40 PM