CODEWITHSUNDEEP
c#entity-frameworklinq
Vendor
Vendor

Reputation: 3

Get an entity with child collection filtered

I have an entity Position that has list of Good

Position
{
public int id { get; set; }
//other properties
public virtual ICollection<Good> Good { get; set; }
}

Good
{
public int id { get; set; }
public string name { get; set; }
public int positionId { get; set; }    
public virtual Position Position { get; set; }
}

So if I have:
Position 1
Good 1, name = "A"
Good 2, name = "A"
Good 3, name = "B"

Position 2
Good 1, name = "A"
Good 2, name = "D"

Position 3
Good 1, name = "C"
Good 2, name = "D"

And search for Good with name = "A", it must return
Position 1
Good 1, name = "A"
Good 2, name = "A"
Position 2
Good 1, name = "A"

In other words - Position entities with List that contains only filtered Good. How can I achieve this with minimum trips to database and minimum records loaded? Any tips are welcome

Upvotes: 0

Views: 112

Answers (3)

Harald Coppoolse
Harald Coppoolse

Reputation: 30512

Well done! I see you use the proper Entity Framework method to model a one-to-many relation between Positions and Good. Every Position has zero or more Goods, and every Good belongs to exactly one Position.

The only advise I would give is to be consistent in your capitalization (PositionId instead of positionId) and use the plurals correctly: a Position has zero or more Goods (not: a Position has zero of more Good). Consistent usage of this makes reading your queries easier, which enhances maintainability. Besides usage of proper capitalization and pluralization minimizes the need for Attributes and Fluent API.

So you want the sequence of all Positions that have at least one Good with Name equals "A", together with all its Goods that have this name.

I experience that once I've modeled the one-to-many relations correctly in entity framework I seldom use a join anymore. I think in collections that have zero or more items from other collections (Positions that have Goods), or Collections that are part of other elements (Goods that belong to a position). Entity Framework will translate this into a join.

Your query would be:

var result = myDbContext.Positions.Select(position => new                  
    {
        ... // use the Position properties you want in your result for example:
        Id = position.Id,
        // from the collection of goods, take only the goods with name equals "A":
        Goods = position.Goods.Where(good => good.Name == "A"),
    })
    // keep only those items that have at least one Good
    .Where(element => element.Goods.Any());

In words: From every position in the collection of Positions make one new (anonymous type) object with several properties:

  • The properties you want from the Position. In your example this was something like "Position1"
  • The sequence of all Goods from this Position that have a Name "A"

From the resulting collection keep only those elements that have at least one Good.

Entity framework will know that this is done using an SQL join / where / select

Addition after comment: query without anonymous objects

In the example above I created anonymous classes, because that is the easiest and most efficient if you don't want the complete objects. SQL will only do a select on the properties you request.

It might be that anonymous objects are not good enough for you. For instance you want to pass the result of the query to other functions, or you want the result in an object that has Methods and other Properties. In that case you can create a special class that contains the result.

Note that SQL does not know your special classes, so you can only use classes without constructor.

The Select in the query will slightly differ:

class SpecialPosition
{
     public int Id {set; set;}
     public string Name {get; set}
     public ICollection<SpecialGood> Goods {get; set;
}
class SpecialGood
{
     public int Id {set; set;}
     public string Name {get; set}
}
IEnumerable<SpecialPosition> result = myDbContext.Positions
    .Select(position => new SpecialPosition()                 
    {
        Id = position.Id,
        Name = position.Name,
        Goods = position.Goods
            .Select(good => new SpecialGood()
            {
                Id = good.Id,
                Name = good.Name,
            }
            .Where(speicalGood => specialGood.Name == "A"),
    })
    // keep only those items that have at least one Good
    .Where(element => element.Goods.Any());

Try for yourself what happens if you don't create SpecialPositions and SpecialGoods, but Positions and Goods

Upvotes: 1

richej
richej

Reputation: 834

Without knowing your database structure, this seems to be a problem that can be done with a simple Join: https://www.w3schools.com/sql/sql_join_left.asp

Alternatively you could try to solve the problem with a linq GroupBy:

allPositions.SelectMany(p => p.Good)
.Where(g => g.name == "A")
.GroupBy(g => g.Position)
.ToDictionary(x => x.Key,x => x.ToList());

Upvotes: 0

thomas
thomas

Reputation: 1453

How about this?

list.Where(p => p.Good.Any(g => g.Name.Equals("A", StringComparison.OrdinalIgnoreCase)))

(where the "A" should be replaced with a parameter)

Upvotes: 0

Related Questions