CODEWITHSUNDEEP
sqllinq
stuartdotnet
stuartdotnet

Reputation: 3034

Linq Many to many

Could anyone please help with the linq for this problem.

Users have a list of Properties and Properties have a list of Users.

I first do a query to get all Properties with a particular CompanyId. This makes a new list which we'll call MyProperties.

I need to get all Tenants that have a property in the MyProperties list.

For other reasons, I don't have access to the "PropertiesUsers" Join table.

Sorry if it looks like I haven't thought this through, I've been banging my head all day on it.

Upvotes: 0

Views: 62

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109080

Use Intersect:

var myPropertyIds = MyProperties.Select(p => p.PropertyId).ToArray();
var result = Users.Where(u => myPropertyIds.Intersect(
                                 u.Properties.Select(p => p.PropertyId))
                              .Any());

If you are sure that the properties in both lists are the same instances you could use

var result = Users.Where(u => MyProperties.Intersect(
                                 u.Properties)
                              .Any());

Upvotes: 1

RePierre
RePierre

Reputation: 9566

You can use Enumerable.SelectMany() to flatten the hierarchy:

var myProperties = dbContext.Properties.Where(property => property.CompanyId = companyId);
var tenants = myProperties.SelectMany(property => property.Tenants);

Upvotes: 1

Related Questions