CODEWITHSUNDEEP
c#entity-frameworklinq
DarthVader
DarthVader

Reputation: 55022

Left join with linq

I have a Activities model as follows

public class Activity
{

  // this is email
  public string CreatedBy {set;get;}
  // Relationship
  public ApplicationUser User{set;get;}
}

Then I have the User model:

public class ApplicationUser
{
   // ID from Identity
   public string Id{set;get;}
   public string Email {set;get;}
}

Of course I have the corresponding tables in the database.

What i need to find out is the users who didnt do any activity.

While the code below works, it is not efficient and times out. Because I have 500K activity in the Activities table.

var userz = _db.Users.AsNoTracking();

var groupedUsers = _db.Activities.AsNoTracking().GroupBy(x => x.CreatedBy).Select(group => new { CreatedBy = group.Key, Count = 1 }).Select(x=>  x.CreatedBy);

var result = userz.Where(x => groupedUsers.Contains(x.Email) == false);

I tried the same Query for Navigation property, which is indexed, ie: User above. Yet the query times out.

Is there a more efficient solution for this using left join?

Upvotes: 0

Views: 70

Answers (1)

fhogberg
fhogberg

Reputation: 415

You should be better of with foreign keys but if this is really how your classes look you could try

_db.Users.Where(u => !_db.Activities.Any(u => a.ApplicationUser == u));

Upvotes: 1

Related Questions