LINQ similar queries totally different execution time

Question

I have very strange problem with Entity Framework and Linq. My first query looks like this:

    from account in context.account
    join access in context.access on account.Id equals access.IdAccount
    join group in context.group on access.IdGroup equals group.Id
    where account.IdUser == idUser
    && access.Date > DateTime(2014,02,21)
    && group.Access > 0
    select access

executes without any problems. But the same question after adding additional condition (access.Status > 0) executes very long. After 30 sec it is still running:

    from account in context.account
    join access in context.access on account.Id equals access.IdAccount
    join group in context.group on access.IdGroup equals group.Id
    where account.IdUser == idUser
    && access.Status > 0
    && access.Date > DateTime(2014,02,21)
    && group.Access > 0
    select access

Status column isn't a computed column. SQL queries returned from

    ((System.Data.Objects.ObjectQuery)result).ToTraceString();

are totally different. Where could be a problem with this additional access.Status condition in query?

Generated SQL queries. Name of columns are different because in LINQ examples they are translated from polish to english. For the first one is:

    SELECT
    `Filter1`.`IdDostep`, 
    `Filter1`.`IDKONTO1` AS `IdKonto`, 
    `Filter1`.`IndeksOms`, 
    `Filter1`.`IdGrupa`, 
    `Filter1`.`DataUtw`, 
    `Filter1`.`DataMod`, 
    `Filter1`.`DataOd`, 
    `Filter1`.`DataDo`, 
    `Filter1`.`Znacznik`, 
    `Filter1`.`Kalendarz`, 
    `Filter1`.`Komplet`, 
    `Filter1`.`Access`
    FROM (SELECT
    `Extent1`.`IdKonto`, 
    `Extent1`.`IdAbonenta`, 
    `Extent1`.`NrAbonenta`, 
    `Extent1`.`NrFaktury`, 
    `Extent1`.`DataFaktury`, 
    `Extent1`.`Login`, 
    `Extent1`.`Password`, 
    `Extent1`.`Notatka`, 
    `Extent1`.`IPAdres`, 
    `Extent1`.`Email`, 
    `Extent1`.`LastLogin`, 
    `Extent1`.`LastIP`, 
    `Extent1`.`Session`, 
    `Extent1`.`Regulamin`, 
    `Extent1`.`RegulaminDataAkceptacji`, 
    `Extent1`.`Powiadomienie`, 
    `Extent1`.`Gratis`, 
    `Extent1`.`PrzyEmail`, 
    `Extent1`.`PrzyIdPytanie`, 
    `Extent1`.`PrzyOdpowiedz`, 
    `Extent1`.`PrzyStatus`, 
    `Extent1`.`PrzySesja`, 
    `Extent1`.`IdKod`, 
    `Extent1`.`szerokosc`, 
    `Extent1`.`wysokosc`, 
    `Extent1`.`serwis`, 
    `Extent1`.`Nzam`, 
    `Extent1`.`parent`, 
    `Extent1`.`RegulaminLogowanie`, 
    `Extent1`.`NoBaners`, 
    `Extent1`.`KontoPromocyjne`, 
    `Extent2`.`IdDostep`, 
    `Extent2`.`IdKonto` AS `IDKONTO1`, 
    `Extent2`.`IndeksOms`, 
    `Extent2`.`IdGrupa`, 
    `Extent2`.`DataUtw`, 
    `Extent2`.`DataMod`, 
    `Extent2`.`DataOd`, 
    `Extent2`.`DataDo`, 
    `Extent2`.`Znacznik`, 
    `Extent2`.`Kalendarz`, 
    `Extent2`.`Komplet`, 
    `Extent2`.`Access`
    FROM `konto` AS `Extent1` INNER JOIN `dostep` AS `Extent2` ON `Extent1`.`IdKonto` = `Extent2`.`IdKonto`
     WHERE `Extent2`.`DataDo` > @gp1) AS `Filter1` INNER JOIN `grupa` AS `Extent3` ON `Filter1`.`IdGrupa` = `Extent3`.`IdGrupa`
     WHERE (`Filter1`.`NrAbonenta` = @p__linq__0) AND (`Extent3`.`Access` > 0)

For the second one is:

    SELECT
    `Filter1`.`IdDostep`, 
    `Filter1`.`IDKONTO1` AS `IdKonto`, 
    `Filter1`.`IndeksOms`, 
    `Filter1`.`IdGrupa`, 
    `Filter1`.`DataUtw`, 
    `Filter1`.`DataMod`, 
    `Filter1`.`DataOd`, 
    `Filter1`.`DataDo`, 
    `Filter1`.`Znacznik`, 
    `Filter1`.`Kalendarz`, 
    `Filter1`.`Komplet`, 
    `Filter1`.`Access`
    FROM (SELECT
    `Extent1`.`IdKonto`, 
    `Extent1`.`IdAbonenta`, 
    `Extent1`.`NrAbonenta`, 
    `Extent1`.`NrFaktury`, 
    `Extent1`.`DataFaktury`, 
    `Extent1`.`Login`, 
    `Extent1`.`Password`, 
    `Extent1`.`Notatka`, 
    `Extent1`.`IPAdres`, 
    `Extent1`.`Email`, 
    `Extent1`.`LastLogin`, 
    `Extent1`.`LastIP`, 
    `Extent1`.`Session`, 
    `Extent1`.`Regulamin`, 
    `Extent1`.`RegulaminDataAkceptacji`, 
    `Extent1`.`Powiadomienie`, 
    `Extent1`.`Gratis`, 
    `Extent1`.`PrzyEmail`, 
    `Extent1`.`PrzyIdPytanie`, 
    `Extent1`.`PrzyOdpowiedz`, 
    `Extent1`.`PrzyStatus`, 
    `Extent1`.`PrzySesja`, 
    `Extent1`.`IdKod`, 
    `Extent1`.`szerokosc`, 
    `Extent1`.`wysokosc`, 
    `Extent1`.`serwis`, 
    `Extent1`.`Nzam`, 
    `Extent1`.`parent`, 
    `Extent1`.`RegulaminLogowanie`, 
    `Extent1`.`NoBaners`, 
    `Extent1`.`KontoPromocyjne`, 
    `Extent2`.`IdDostep`, 
    `Extent2`.`IdKonto` AS `IDKONTO1`, 
    `Extent2`.`IndeksOms`, 
    `Extent2`.`IdGrupa`, 
    `Extent2`.`DataUtw`, 
    `Extent2`.`DataMod`, 
    `Extent2`.`DataOd`, 
    `Extent2`.`DataDo`, 
    `Extent2`.`Znacznik`, 
    `Extent2`.`Kalendarz`, 
    `Extent2`.`Komplet`, 
    `Extent2`.`Access`
    FROM `konto` AS `Extent1` INNER JOIN `dostep` AS `Extent2` ON `Extent1`.`IdKonto` = `Extent2`.`IdKonto`
     WHERE ((`Extent2`.`Komplet`) > 0) AND (`Extent2`.`DataDo` > @gp1)) AS `Filter1` INNER JOIN `grupa` AS `Extent3` ON `Filter1`.`IdGrupa` = `Extent3`.`IdGrupa`
     WHERE (`Filter1`.`NrAbonenta` = @p__linq__0) AND (`Extent3`.`Access` > 0)     

Answer 1

The problem shouldn't be in linq but the underlying execution of the sql statement in the database. Probably the first query executes using just indexes, while the second scans the table.

Suggestion to solve your issue, see the execution plan of both queries, using the ToTraceString() and understand your problem. If you need extra help, post that queryplans and we will give you a hand.

EDIT:

As you might see from the sql queries, they aren't that different, they only differ in the inner query filter:

The first query has this:

    WHERE  Extent2.datado > @gp1) AS Filter1 

the second one:

    WHERE  ( ( Extent2.komplet ) > 0 ) 
           AND ( Extent2.datado > @gp1 )) AS Filter1 

So, even though you have everything index doesn't mean they are good indexes. If you have an index for each of the columns SQL might choose only one of them. Probably you'll need to review them. So, like i suggested earlier, check the query execution plan of each and see where's your bottleneck.