Join/Where With Linq and Lambda

Join/Where with LINQ and Lambda

I find that if you're familiar with SQL syntax, using the LINQ query syntax is much clearer, more natural, and makes it easier to spot errors:

var id = 1;
var query =
   from post in database.Posts
   join meta in database.Post_Metas on post.ID equals meta.Post_ID
   where post.ID == id
   select new { Post = post, Meta = meta };

If you're really stuck on using lambdas though, your syntax is quite a bit off. Here's the same query, using the LINQ extension methods:

var id = 1;
var query = database.Posts    // your starting point - table in the "from" statement
   .Join(database.Post_Metas, // the source table of the inner join
      post => post.ID,        // Select the primary key (the first part of the "on" clause in an sql "join" statement)
      meta => meta.Post_ID,   // Select the foreign key (the second part of the "on" clause)
      (post, meta) => new { Post = post, Meta = meta }) // selection
   .Where(postAndMeta => postAndMeta.Post.ID == id);    // where statement

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

You create an anonymous type with both objects before your where clause and filter it on ContactOperationPlaces value. You just have to select the 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 => o.c)
                                    .Distinct();

LINQ Lambda Left join with an Inner join

Inner joins are performed with the Join method. I think your query should go something like this:

var query = db.staff
              .GroupJoin(db.training,
                         s => s.id,
                         t => t.staff_id,
                         (s, t) => new { Staff = s, Training = t.FirstOrDefault() })
              .Join(db.manager,
                    gj => gj.Staff.manager_id,
                    m => m.id,
                    (gj, m) => new { Staff = gj.Staff, Training = gj.Training, Manager = m })
              .Where(st => st.Training == null
                        && st.Manager.id == managerId);

Lambda where condition in Join statement

The equivalent for this:

var empinadept = (from e in employeeCollection
              from dep in deptCollection
              where e.Id == dep.EmployeeId
              && dep.DepetarmentName == "a"
              select e)
             .ToList();

Is this:

var result = employeeCollection.Join(deptCollection,
        e => e.Id,
        dep => dep.EmployeeId,
        (e,dep) => new { e, dep })
    .Where(item => item.dep.DepetarmentName == "a")
    .Select(item => item.e)
    .ToList();

A better option will be to:

var result = employeeCollection.Join(
            deptCollection.Where(dep => dep.DepetarmentName == "a"),
            e => e.Id,
            dep => dep.EmployeeId,
            (e,dep) => e)
       .ToList();

Closest to the query-syntax (but I would say that is less nice opinion based) is:

var result = employeeCollection.Join(
        deptCollection,
        e => new { e.Id, "a" },
        dep => new { dep.EmployeeId, dep.DepartmentName },
        (e,dep) => e).ToList();

Linq to SQL left outer join using Lambda syntax and joining on 2 columns (composite join key)

I was able to get this LEFT OUTER JOIN on the composite foreign key pair barcode, event_id working in both Linq2Sql, and Entity Framework, converting to lambda syntax as per this query syntax example.

This works by creating an anonymous projection which is used in match of the left and right hand sides of the join condition:

var dnrs = context.participants.GroupJoin(
    context.prereg_participants,
    x => new { JoinCol1 = x.barcode, JoinCol2 = x.event_id }, // Left table join key
    y => new { JoinCol1 = y.barcode, JoinCol2 = y.event_id }, // Right table join key
    ...

Notes

This approach relies on the automagic equality given to identical anonymous classes, viz:

Because the Equals and GetHashCode methods on anonymous types are defined in terms of the Equals and GetHashCode methods of the properties, two instances of the same anonymous type are equal only if all their properties are equal.

So for the two projections for the join keys need to be of the same type in order to be equal, the compiler needs to see them as the same anonymous class behind the scenes, i.e.:

• The number of joined columns must be the same in both anonymous projections
• The field types must be of the same type compatable
• If the field names differ, then you will need to alias them (I've used JoinColx)

I've put a sample app up on GitHub here.

Sadly, there's no support yet for value tuples in expression trees, so you'll need to stick to anonymous types in the projections.

C# linq lambda join and select syntax

var query = persons.join(address,
    per = person.addressId,
    add = addressId
    (per,add) => 
    {
        per.addressDescription = add.addressDescription;
        return per;
    });

How can I create an Lambda Expression for Linq in the Where clause for two tables after the Join?

I suspect calling Compile() on your Expression is causing your trouble. Your full query includes the Join, but you've already compiled the Where clause, so it can't compile the entire query including the Join together. That might be why it's grabbing the entire table because it's executing the Where by itself first, then doing the Join later.

But you don't need to call Compile(). Just pass the Expression into Where():

Expression<Func<MyCourse, bool>> filter = mc => mc.Active == 1;
var myClasses = db.MyCourse
    .Where(filter)
    .Join(db.People, mc => mc.PersonId, 
p => p.PersonId, (mc, p) => new { MyCourse= mc, Person = p }).ToList();

Somewhat unrelated to your actual problem, but if you created the foreign keys, you can simplify this a bit. Update your model in your Visual Studio project if you haven't already. Your Person class will change to have a list of MyCourse and your MyCourse class will have a list of Person.

So you can do something like:

Expression<Func<MyCourse, bool>> filter = mc => mc.Active == 1;
var courses = db.MyCourse.Include("Person").Where(filter);
foreach (var course in courses) {
    var person = course.Person; //This is populated with the Person record
}

Linq handles the join, and each MyCourse returned will have a Person property.

---

Related Topics