CODEWITHSUNDEEP
entity-framework
user5206903
user5206903

Reputation: 146

how to implement EF inner join with given filter?

SELECT DISTINCT k.* FROM [dbo].[kinds] K 
INNER JOIN KindGraphic KG ON K.KindId = KG.KindId
INNER JOIN Graphics G ON KG.GraphicId = G.GraphicId
WHERE K.CategoryType = 2
AND G.IsSpecial = 1

How to write this in EF ? I am new to EF. I m using dbContex for my MVC project.

Make Note that "KindGraphic" table is mapped liked this ways

so I can not use this method https://stackoverflow.com/a/21986882/3264939

 modelBuilder.Entity<Kind>()
                .HasMany(c => c.Graphics)
                .WithMany(g => g.Kinds)
                .Map(t => t.MapLeftKey("KindId")
                    .MapRightKey("GraphicId")
                    .ToTable("KindGraphic"));

Upvotes: 1

Views: 693

Answers (1)

Hopeless
Hopeless

Reputation: 4773

The result from your original query is some kind of complex result. So without selecting the exact columns (instead of using *), I assume the result is contained in an anonymous type like this:

{
    Kind,        
    Graphic
}

I understand that KindGraphic is some kind of junction (join) table, so it's info is not important to include in the result (we can access KindId from Kind and GraphicId from Graphic). Here is the LINQ query:

var result = context.kinds.Where(e => e.CategoryType == 2)
                    .SelectMany(e=> e.Graphics.Where(g=>g.IsSpecial == 1), 
                                (e, g) => new { Kind = e, Graphic = g} );

After your edit to use distinct, the query can be translated as you want all kinds having category type = 2 and any Graphics with IsSpecial = 1. So it should be like this:

var result = context.kinds.Where(e => e.CategoryType == 2 && 
                                      e.Graphics.Any(g=>g.IsSpecial == 1));

Upvotes: 1

Related Questions