Radim Motycka

Radim Motycka

  • NA
  • 31
  • 31.9k

EF creates mad sql query

May 29 2013 11:12 AM

Hello,
Pls could you advice? I have linq query below nad t-sql query generated by EF caught with SQL profiler. Could you advise how to rewrite Linq query. Because it takes unbelievably long time to get this data. And In generated SQL query it firstly gets all data and filters them in second step. How to manage it to use where sooner.

Thx 

       public IList<ScsDebtor> GetDebtorData()
        {
            var query = _delfinEntities.ForAposteriori.Where(o => o.spr_CalyNumerSprawy == "SCS/3/307").Select(o => new ScsDebtor()
                             {
                                 AdrAdres = o.adr_Adres,
                                 AdrKodPocztowy = o.adr_KodPocztowy,
                                 AdrMiasto = o.adr_Miasto,
                                 DlzPesel = o.dlz_PESEL,
                                 KntAdres = o.knt_Adres,
                             });

            return query.ToList();
        }


SELECT TOP (1)
1 AS [C1],
[Extent1].[adr_Adres] AS [adr_Adres],
[Extent1].[adr_KodPocztowy] AS [adr_KodPocztowy],
[Extent1].[adr_Miasto] AS [adr_Miasto],
[Extent1].[dlz_PESEL] AS [dlz_PESEL]
FROM
(SELECT
[ForAposteriori].[knt_Adres] AS [knt_Adres],

      [ForAposteriori].[knt_Miasto] AS [knt_Miasto],

      [ForAposteriori].[knt_KodPocztowy] AS [knt_KodPocztowy],

      [ForAposteriori].[dlz_PESEL] AS [dlz_PESEL]
 
FROM [dbo].[ForAposteriori] AS [ForAposteriori]) AS [Extent1]
WHERE N'XXXX' = [Extent1].[spr_CalyNumerSprawy]


Answers (2)