CODEWITHSUNDEEP
c#linqentity-frameworkentity-framework-4
GibboK
GibboK

Reputation: 73938

How to filtering related entities but with no foreign keys

I am using EF with lambda expression to query a table.

var eventToPushCollage = eventsForEvaluation.Where(x => x.DateTimeStart > currentDateTime && currentDateTime >= x.DateTimeStart.AddMinutes(-15));

Table eventsForEvaluation has a property Id.

In my DB I have also another table called PushedEvents with an Id property.

Note: the two tables have no a Foreign Key relationship.

I need to apply another filtering to my query, and fetch all the records that are also NOT present in the second table PushedEvents.

I would like to know if it is possible and a sample code.

Upvotes: 1

Views: 462

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236268

Use group join:

var eventToPushCollage = 
      from e in eventsForEvaluation
      join p in PushedEvents on e.Id equals p.Id into g
      where e.DateTimeStart > currentDateTime &&
            currentDateTime >= e.DateTimeStart.AddMinutes(-15) &&
            g.Count() == 0 // NOT present in second table
      select e;

UPDATE (method syntax)

var eventToPushCollage = eventsForEvaluation
       .GroupJoin(PushedEvents,
                  e => e.Id,
                  p => p.Id,
                  (e,g) => new { e, g })
       .Where(x => x.e.DateTimeStart > currentDateTime &&
                   currentDateTime >= x.e.DateTimeStart.AddMinutes(-15) &&
                   x.g.Count() == 0)
       .Select(x => x.e);

Upvotes: 2

Jan P.
Jan P.

Reputation: 3297

Try this

var eventToPushCollage =
    from x in eventsForEvaluation 

    where 
    !PushedEvents.Any(item => item.Id == x.Id)
    && x.DateTimeStart > currentDateTime 
    && currentDateTime >= x.DateTimeStart.AddMinutes(-15)

    select x;

Upvotes: 1

Related Questions