LINQ for many-to-many in EF Core 2

Question

I have searched on google and stackoverflow for a while and yes I have found many articles on the subject but I still can't seem to know what I am doing wrong.

Problem: I do have a "link" table (many-to-many) between three tables: - check - car - gate

My 'link' class looks like follows:

public class CheckCarGate
{
    public int CheckId { get; set; }
    public Check Check{ get; set; }

    public int CarId { get; set; }
    public Car Car{ get; set; }

    public int GateId { get; set; } 
    public Gate Gate { get; set; } 
}

DbContext:

public virtual DbSet<Car> Cars{ get; set; }
public virtual DbSet<Gate> Gates { get; set; }
public virtual DbSet<Check> Checks{ get; set; }

// The key:

protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<CheckCarGate>().HasKey(p => new { p.CheckId, p.CarId, p.GateId });
    }

I did follow this documentation https://learn.microsoft.com/en-us/ef/core/modeling/relationships#many-to-many when creating models. CheckCarGate is a navigation property in the entity models. E.g.

public class Gate
{
    public int GateId { get; set; }
    public string Descr { get; set; }

    public ICollection<CheckCarGate> CheckCarGates { get; set; }
}

Code first created this table in the database. Now I'm trying to select all Checks with all Gates for one particular CarId.

I tried something like this:

var masterlist = _context.Checks.Where(p => p.CheckCarGate.Any(x => x.CarId == 12));

or:

var masterlist = _context.Checks
                .Include(p => p.CheckCarGate)
                .ThenInclude(p=>p.Gate)
                .Include(p=>p.CheckCarGate)
                .ThenInclude(p=>p.Car);

//Edit: I'm sorry for not giving enough information! Bear with me...

Could someone point me to the right direction?

Many thanks in advance!

N.

Can you include your mappings? (EntityTypeConfiguration or modelBuilder configuration on the DbContext) Also what issue are you seeing? Error, or empty values? — Steve Py, May 23 '18 at 21:06
`_context.CheckCarGates.Include(x => x.Check).Include(x => x.Gate).Where(x => x.CarId == 12)).Select(x => new { x.Gate, x.Check });` — Brad, May 23 '18 at 23:48

esoterik · Answer 1 · 2018-05-24T00:35:24.913

0

I'm not sure there is enough information here to answer your question; it appears like each check has its own checkcargate, which doesn't really make sense.

Assuming you had a table of checks and CheckCarGate table seperatatly, you can use where and select to make this query.

var out = CheckCarGateTable.Where(t => t.CarID == targetCarID)
          .Select(c => checks.getbyid(c.CheckID))

or, based on your class definition

var out = CheckCarGateTable.Where(t => t.CarID == targetCarID)
          .Select(c => c.Check))

First we get all CheckCarGates that refer to our target car, then we transform these into checks using the select call; out should be of type IEnumerable< Check >

and process the list like:

foreach (Check c in out)
{
     proc(c);
}

This is the best I can do with the information given.

edited May 24 '18 at 00:35

answered May 23 '18 at 23:37

esoterik

245
1
7

Thank you for your answer and sorry for not providing enough information. As the CheckCarGate table is a 'link' table, I cannot use it with _context.xxxxx. It is a navigation property – Nearshore May 24 '18 at 04:59
@Nearshore That's your misconception. There's nothing that keeps you from including a property `DbSet` in the context. – Gert Arnold May 24 '18 at 19:22

score 0 · Answer 2 · answered May 24 '18 at 18:54

Your OnModelCreating(...) should have more in it defining the relationship

protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<CheckCarGate>().HasKey(p => new { p.CheckId, p.CarId, p.GateId });

        builder.Entity<CheckCarGate>().HasOne(c => c.car)
                                      .WithMany(c => c.check)
                                      .HasForeignKey(k => k.carId)

        ...
    }

I'm not sure how you change your query after that, the documentation link you provided didn't have example usage.

score 0 · Answer 3 · answered May 25 '18 at 05:08

0

I gave up looking for a LINQ solution. (2 days wasted).

Instead I decided to use raw sql and store the data in a new object.

Credits to: https://stackoverflow.com/users/355714/pius and his solution: https://stackoverflow.com/a/46013305/848642

answered May 25 '18 at 05:08

Nearshore

149
3
15

LINQ for many-to-many in EF Core 2

3 Answers3