C# Join by Linq how should pass innerKeySelector?

.net-core c# entity-framework-core linq

Question

how should pass innerKeySelector?

have:

model1 - {int model1 Id, int model2Id, model2 Model2}
model2 - {int model2Id, List<model1> modelsRef, ...(other)}

need to do:

query.Join(context.model2, x => x.Model2, y => y.modelsRef, (x,y) => new{
                Id = x.mdel1Id,
                (other)...}).Select();

have compile error:

{
    ...
    "message": "The type arguments for method 'Enumerable.Join<TOuter, TInner, TKey, TResult>(IEnumerable<TOuter>, IEnumerable<TInner>, Func<TOuter, TKey>, Func<TInner, TKey>, Func<TOuter, TInner, TResult>)' cannot be inferred from the usage. Try specifying the type arguments explicitly. [ProjectName]",

}
1
0
11/5/2018 12:29:31 PM

Accepted Answer

So you have a sequence of Model2s, where every Model2 has zero or more Model1s. Every Model1 belongs to exactly one Model2, using foreign key Model1Id, a fairly standard one-to-many relation.

When using entity framework you will have something similar to:

class Model2
{
    public int Id {set; set;}

    // every Model2 has zero or more Model1s:
    public virtual ICollection<Model1> Model1s {get; set;}

    ...
}

class Model1
{
    public int Id {set; set;}

    // every Model1 belongs to exactly one Model2, using foreign key
    public int Model2Id {get; set;}
    public virtual Model2 Model1 {get; set;}
    ...
}

Remember:

In entity framework the columns of the tables are represented by the non-virtual properties of the classes. The virtual properties represent the relations between the tables.

Note: I have changed your List<Model1> Model1s into an ICollection<Model1> Model1s. After all, are you sure it is a List? What wouldModel1s[4] mean?

You want to join the model1s and model2s using a standard inner join:

var result = dbContex.Model1s
    .Join(dbContext.Model2s,   // join Model1s and Model2s
    model1 => model1.Model2Id, // from every Model1 take the foreign key Model2Id
    model2 => model2.Id,       // from every Model2 take the primary key Id
    (model1, model2) => new    // when they match use the matching models to create
    {                          // a new object with the following properties
        SomeProperty = model1.PropertyA,
        SomeOtherProperty = model2.PropertyB,
        MyX = model2.PropertyC,
        MyY = model1.PropertyD,
        ....
    });

Use virtual properties instead of Joins!

Several people commented that you don't need to use joins when using entity framework. They are right. You can get the same result using the collections. The result feels more natural and is a much more readable:

var result = dbContext.Model1s.Select(model1 => new
{
     SomeProperty = model1.PropertyA,
     SomeOtherProperty = model1.Model2.PropertyB,
     MyX = model1.Model2.PropertyC,
     MyY = model2.PropertyD,
});

Entity framework knows your one-to-many relationship and will do the inner join for you.

To make a GroupJoin:
If you have a parent-children one-to-many relationship and you need certain "parents with all or some of their children", then start with the Parent and use the ICollection to get the Children. This will become a GroupJoin.

To make an Inner Join
If you need some "Children with their Parents", then start with the Child and use the Parent property. This will become an inner join

0
11/6/2018 10:00:09 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow