CODEWITHSUNDEEP
entity-framework.net-coreentity-framework-core
OddBeck
OddBeck

Reputation: 845

How to do query in EF Core / Dotnet Core without having it resorting to a join

I have two tables:

// pseudo code:

class User {
  int UserID; // primary key
  string name;
  IEnumerable<Checklist> Checklists;
}

class Checklist {
  int ChecklistID; // primary key
  String name;
}

And when I do the following LINQ query:

        var result = from checklist in _context.Users
            where checklist.UserID == id 
            select checklist.Checklists;

It always resorts to 2 queries, one for the UserID, and then a Select + JOIN:

Select .... from Users where ..UserID=@__ID
 .......
Select ... from Checklists C
INNER JOIN ( Select ... from User 
     where 
     UserID = @__id_0
     .....
) as T on C.UserID = T.UserID;

The thing is that I already have the ID of the user, so I'd rather make EF do a simple query like this: 

Select ... from Checklists where UserID = 1;

instead of querying for the User with it's ID, and then use that UserID in a new query for Checklist where UserID = that ID

Upvotes: 1

Views: 187

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205589

EF Core is much flexible than EF6 in that regard. Additionally to explicitly defined properties it also allows you to use shadow properties inside LINQ query through EF.Property method:

Addresses a given property on an entity instance. This is useful when you want to reference a shadow state property in a LINQ query.

So with your original model you could use:

var result = from checklist in _context.Checklists
             where EF.Property(checklist, "UserId") == id;

Of course you can always add explicit navigation and/or FK property in the model, but it's good to have this option in case you don't want to do that (neither navigation nor FK property is mandatory).

Upvotes: 2

Henk Holterman
Henk Holterman

Reputation: 273199

Navigation properties are the mainstay of EF. So your details class should expose its foreign key properties. There is no point in omitting them. 

class Checklist {
  int ChecklistID; // primary key

  int UserId { get; set; }  // Foreign Key, the std naming patterns will match these
  User User { get; set; }

  String name;
}

Upvotes: 1

Related Questions