CODEWITHSUNDEEP
c#linqincludeentity-framework-core
Mono
Mono

Reputation: 742

EF Core Select one Entity filtered by related entities

I have a question and I'm struggling to find the answer.

I have 3 Entity classes with a relation like that (simplified):

class Article {
 public int id
 public string Name
 public int ArticleStandard Id
 public int CompanyId
 public ArticleStandard ArticleStandardNavigation
 public Company CompanyNavigation
}
class ArticleStandard {
 public int id
 public ICollection<Article> Articles
}
class Company {
 public in id
 public ICollection<Article> Articles
}

So the relation is 1 company can have many articles, 1 standardarticle can have many articles. They are all setup as DBSets in the DBContext. Given is the Id of the ArticleStandard. I now want to get all companies who have this standard article through the article entity. The TSQL I would like to "produce" is:

  select c.* from Company c
  inner join Article a on c.ID = a.CompanyId
  inner join ArticleStandard on a.ArticleStandard = arts.id
  Where arts.Id = 1

Which gives the result I want to have.

I tried a lot around and I don't want to post all my trials to work around the issue, that could make it work with just Include and ThenInclude. But I dont want to get all Articles and ArticleStandard. If I only select the company the Includes are ignored: https://learn.microsoft.com/en-us/ef/core/querying/related-data#ignored-includes

var vtp = context.Company.Include(a => a.Article).ThenInclude(ars => ars.ArticleStandardNavigation).ToList();

Here I also struggle to even use a where clause on the included entities. I read about that issue here: How to add where clause to ThenInclude

But I couldnt make it really work, especially only retrieving the company and not the other entities.

I know I could load all and just take the companies out. That would work. But I want to reduce the amount of data sent and also keep the query as one.

Any hints? I'm still pretty inexperienced with EF Core and LinQ is also still sometimes confusing for me.

If you need more information or can point me to a similar problem (I found some kind of similar cases, but couldn't use them correctly) I would be very thankful.

Thank you for your time.

Upvotes: 3

Views: 6373

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205899

Well, forget about SQL and joins. In EF (Core) targeting LINQ queries you use the navigation properties to access the related data. In the context of the query, the entities represent the database table records and navigations - joins and related table records.

For reference navigation properties you use simple criteria like:

Where(entity => entity.Reference.SomeProperty == someValue)

and for collection navigation properties, usually Any with the criteria needed, e.g.:

Where(entity => entity.Collection.Any(related => related.SomeProperty == someValue))

in other words, I want records having at least one related record with this value.

Applying the above rules to your model, the equivalent query would be like this:

var query = db.Companies
    .Where(c => c.Articles.Any(a => a.ArticleStandardNavigation.id == 1));

The generated SQL most likely will not be the same as the one you would write by hand (in general we cannot control the SQL generation of the ORM), but the result should be.

Btw, there is no need to append Navigation to the navigation property names. The default (and more intuitive) convention is to use the class name, e.g.

public class Article
{
    // ...
    public ArticleStandard ArticleStandard { get; set; }
    public Company Company { get; set; }
}

Upvotes: 8

Related Questions