How to add where clause to linq join (lambda)?

Question

I have two database tables. Contact (Id, Name, ...) and ContactOperationalPlaces (ContactId, MunicipalityId), where the contact can be connected to several contact operations.

What I'm trying to do is build a query (ASP.NET, C #) using IQueryable, which selects only all the contacts that exist in the ContactOperationalPlaces table with the given MunicipalityId.

The sql query looks like this:

select * from Contacts c right join ContactOperationPlaces cop on c.Id = cop.ContactId where cop.MunicipalityId = 301; 

With linq, it will look something like this:

 //_ctx is the context var tmp = (from c in _ctx.Contacts join cop in _ctx.ContactOperationPlaces on c.Id equals cop.ContactId where cop.MunicipalityId == 301 select c); 

So, I know how to do this, if I had to immediately select all of this, unfortunately, this is not so. I create a query based on user input, so I don’t know all the choices at once.

This is what my code looks like:

 IQueryable<Contacts> query = (from c in _ctx.Contacts select c); //Some other logic.... /*Gets a partial name (string nameStr), and filters the contacts so that only those with a match on names are selected*/ query = query.Where(c => c.Name.Contains(nameStr); //Some more logic //Gets the municipalityId and wants to filter on it! :( how to? query = query.where(c => c.ContactOperationalPlaces ...........?); 

The difference is with two statements that with the first, each contact has only one name, but with the last contact can contain several jobs ...

I managed to find one solution, but this solution gives me an unidentified object containing both tables. And I do not know how to do this.

 query.Join(_ctx.ContactOperationPlaces, c => c.Id, cop => cop.ContactId, (c, cop) => new {c, cop}).Where(o => o.cop.municipalityId == 301); 

The object returned from this expression is System.Linq.Iqueryable <{c: Contact, cop: ContactOperationalPlace}> and cannot be passed to Contacts ...

So this is the problem. The answer is probably pretty simple, but I just can't find it ...

Answer 1

You create an anonymous type with both objects before your where clause and filter it by ContactOperationPlaces value. You just need to select Contact after that.

 query.Join(_ctx.ContactOperationPlaces, c => c.Id, cop => cop.ContactId, (c, cop) => new {c, cop}).Where(o => o.cop.municipalityId == 301) .Select(o => oc) .Distinct(); 

Answer 2

You do not need to return new objects to the result selection function. The delegate provides both variables so that you can select one or the other option or some other option (which requires a new object). Try the following:

 query.Join(_ctx.ContactOperationPlaces, c => c.Id, cop => cop.ContactId, (c, cop) => c).Where(o => o.cop.municipalityId == 301); 

Answer 3

can you just apply it to var and try using intellisense on it?

  var myCast = query.Join(_ctx.ContactOperationPlaces, c => c.Id, cop => cop.ContactId, (c, cop) => new {c, cop}).Where(o => o.cop.municipalityId == 301); 

Just a thought

Answer 4

I think it would be much easier if you start this as two different requests and then combine them. I assume that the Contact (1 ↔ many) Contactoperationplaces relationship? And, in the end, will you show 1 element per contact place, and not 1 element per contact?

Do it like this:

 IQueryable<Contacts> query = (from c in _ctx.Contacts select c); ... query = query.Where(x=> x.Name.ToLower().Contains(nameStr.ToLower()); ... IQueryable<ContactOperationPlaces> query_2 = (from c in _ctx.ContactOperationPlaces where query.Where(x=> x.Name == c.Contact.Name).Count() > 0 select c); //Now query_2 contains all contactoperationsplaces which have a contact that was found in var query 

Conversely, there is a much simpler way to do this, and that, completely skipping the first part.

 IQueryable<ContactOperationPlaces> query_2 = (from c in _ctx.ContactOperationPlaces where c.Contact.Name.ToLower().Contains(strName.ToLower()) select c); 

If you use the Entity Framework, you do not need to do any joins while you define associations between tables.

Now, when I look at this, my second solution is much more efficient and simpler. But if you need to do some other processing between these commands, the solution works too :)

If you need more explanation, feel free to ask :)