SQL - listes d'enfant comparer des objets à l'aide de Linq et c# - Stack Overflow

I’m having trouble formulating a query for a custom business object search engine I’m writing and I’m hoping someone here might be able to help me out. I’ll do my best to describe the problem.

Here are the database tables I’m working with and am accessing via Entity Framework 6 (EDMX):

Contract
------------
ContractUID
ContractDate

ContractEntity
-------------
ContractEntityUID
ContractUID
EntityTypeID

One Contract row can have multiple ContractEntity rows, and thus one Contract can reference multiple EntityTypeIDs.

The search user interface supplies this search engine with, among other search criteria, a list of EntityTypeIDs. The engine is expected to return a list of Contracts which reference any of the EntityTypeIDs in the search request and match any of the other supplied criteria (contract date, for example).

Input search criteria are wrapped in an object like this:

SearchCriteria
{
    DateTime? ContractDate;
    List<int> EntityTypeIDs;
}

Here is some sample code that I've worked out so far:

var d = entities.Contracts.Where(q => q.ContractUID > 0);

if(search.ContractDate.HasValue)
   d = d.Where(q => q.ContractDate == search.ContractDate);

if(search.EntityTypeIDs != null)
{
  d = d.Where(q => q.ContractEntities.Select(q2 => q2.EntityTypeID).Any(search. EntityTypeIDs.Select(i).Contains(…
}

What I’m not clear on is how to apply the lesson described at Comparing two lists using linq to sql to this case. The difference that seems key is that I need to compare two lists which are themselves properties of other objects. Am I missing something obvious here? Any help would be greatly appreciated.

The last Where statement in your code is what I am focussing on:

Get the Contracts that have at least 1 ContractEntity with an EntityTypeId that's in your list.

d = d.Where(q =>
    q.ContractEntities.Any(conEnt => 
        search.EntityTypeIDs.Contains(conEnt.EntityTypeId)));

I’m having trouble formulating a query for a custom business object search engine I’m writing and I’m hoping someone here might be able to help me out. I’ll do my best to describe the problem.

Here are the database tables I’m working with and am accessing via Entity Framework 6 (EDMX):

Contract
------------
ContractUID
ContractDate

ContractEntity
-------------
ContractEntityUID
ContractUID
EntityTypeID

One Contract row can have multiple ContractEntity rows, and thus one Contract can reference multiple EntityTypeIDs.

The search user interface supplies this search engine with, among other search criteria, a list of EntityTypeIDs. The engine is expected to return a list of Contracts which reference any of the EntityTypeIDs in the search request and match any of the other supplied criteria (contract date, for example).

Input search criteria are wrapped in an object like this:

SearchCriteria
{
    DateTime? ContractDate;
    List<int> EntityTypeIDs;
}

Here is some sample code that I've worked out so far:

var d = entities.Contracts.Where(q => q.ContractUID > 0);

if(search.ContractDate.HasValue)
   d = d.Where(q => q.ContractDate == search.ContractDate);

if(search.EntityTypeIDs != null)
{
  d = d.Where(q => q.ContractEntities.Select(q2 => q2.EntityTypeID).Any(search. EntityTypeIDs.Select(i).Contains(…
}

What I’m not clear on is how to apply the lesson described at Comparing two lists using linq to sql to this case. The difference that seems key is that I need to compare two lists which are themselves properties of other objects. Am I missing something obvious here? Any help would be greatly appreciated.

The last Where statement in your code is what I am focussing on:

Get the Contracts that have at least 1 ContractEntity with an EntityTypeId that's in your list.

d = d.Where(q =>
    q.ContractEntities.Any(conEnt => 
        search.EntityTypeIDs.Contains(conEnt.EntityTypeId)));

Source