Roy_Dorsthorst Roy_Dorsthorst - 1 month ago 7
MySQL Question

Using lambda expressions on joined basic LINQ query

When making a basic LINQ query you can later use lambda expressions to add a where clause like this:

query.Where(c => (init.Contains(c.user)));
.

My problem is that I need to add two where clauses on a query that uses a
join
in the basic LINQ query.

I'm trying to replace my old basic LINQ queries with added lambda expressions so that i prevent duplicated code.

This is my code;

var query = from c in db.Clgcom
join u in db.Dvusr
on c.Comaut equals u.Gitusr
// && (initialen.Contains(c.Tstusr) // <-- query.Where(c => (initialen.Contains(c.Tstusr)));
// This is what im trying to replace// ^^ This works because its in the same table
// || initialen.Contains(u.Clgusr)) // <-- What do i type when i want to include both these conditions?
&& (c.Modid.StartsWith("C")
|| c.Modid.StartsWith("M"))
select c;

if(filter != null){
query = query.Where(c => (initialen.Contains(c.Tstusr)
|| initialen.Contains(u.Clgusr)));
// This doesn't work
}


Is there a way to use a lambda expression that would achieve adding these two conditions in my where clause?

Or should i replace ALL basic LINQ queries with using lambda expressions?

Answer

Basically you need to also defer the select by selecting both c and u to begin with and later just selecting c.

var temp = from c in db.Clgcom
           join u in db.Dvusr on c.Comaut equals u.Gitusr
           where c.Modid.StartsWith("C") || c.Modid.StartsWith("M")
           select new {c, u};

if(filter != null){
    temp = temp.Where(x => initialen.Contains(x.c.Tstusr) 
                           || initialen.Contains(x.u.Clgusr));

var query = temp.Select(x => x.c);

If the relationship between Clgcom and Dvusr is many to one then you could do the following as Clgcom should have a Dvusr navigation property based on the foreign key relationship.

var query = from c in db.Clgcom
            where (c.Modid.StartsWith("C") || c.Modid.StartsWith("M")) && c.Dvuser != null
            select c;

if(filter != null){
    query = query.Where(c => initialen.Contains(c.Tstusr) 
                             || initialen.Contains(c.Dvusr.Clgusr));
Comments