What Is the Syntax For an Inner Join in Linq to Sql

What is the syntax for an inner join in LINQ to SQL?

It goes something like:

from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}

It would be nice to have sensible names and fields for your tables for a better example. :)

Update

I think for your query this might be more appropriate:

var dealercontacts = from contact in DealerContact
                     join dealer in Dealer on contact.DealerId equals dealer.ID
                     select contact;

Since you are looking for the contacts, not the dealers.

LINQ syntax for SQL INNER JOINS

You can write you T-SQL query in LINQ using

either query expression syntax:

// Declare query.
var query =
    from e in db.Evidence
    join p in db.Phone on e.PhoneId equals p.PhoneId
    join s in db.State on p.StateId equals s.StateId
    select new {e.PersonId, e.PhoneNoId, e.PhoneId, s.StateId, e.AssignDate, e.DiscardDate, e.Note};
// Execute query.
var result = query.ToList();

or method-based query syntax:

// Declare and execute query.
var result =
    db.Evidence
       .Join(db.Phone, e => e.PhoneId, p => p.PhoneId, (e, p) => new {e, p})
       .Join(db.State, t => t.p.StateId, s => s.StateId, (t, s) =>
          new {t.e.PersonId, t.e.PhoneNoId, t.e.PhoneId, s.StateId, t.e.AssignDate, t.e.DiscardDate, t.e.Note});
       .ToList(); // Method "ToList()" executes query.

In this samples variable db is of type DbContext (I assume that you are going to use EntityFramework).

Refer next links for details:

Join (query expression syntax)
Perform inner joins (query expression syntax)
Join (method-based syntax)
Join method documentation
EntityFramework Core

LINQ syntax for SQL query with multiple inner joins and aliases

LINQ join syntax uses equals keyword instead of = in join condition:

var newUserList = from store in Users
                  join toSupplier in UserRelationship on store.ID equals toSupplier.YId
                  join supplier in Users on supplier.ID equals toSupplier.XId
                  join toFarm in UserRelationship on store.ID equals toFarm.XId
                  join farm in Users on farm.ID equals toFarm.YId
                  select ...

Linq to Sql Inner Join

Doesn't your model have a real association at the entity level?

Meaning, DealerContact having a property to represent the associated Dealer instead of handling just the ids.

You probably don't even need to specify that join manually.
How about:

var dealercontacts = from contact in DealerContact
                     where contact.Branch equals contact.Dealer.Branch
                     select contact;

LINQ Method Syntax with INNER and OUTER Join

I think what you ask for is impossible without returning a new (anonymous) object instead of Group (as demonstrated in this answer). EF will not allow you to get a filtered Course collection inside a Section because of the way relations and entity caching works, which means you can't use navigational properties for this task.

First of all, you want to have control over which related entities are loaded, so I suggest to enable lazy loading by marking the Sections and Courses collection properties as virtual in your entities (unless you've enabled lazy loading for all entities in your application) as we don't want EF to load related Sections and Courses as it would load all courses for each user anyway.

public class Group {
    public int Id { get; set; }
    public int Name { get; set; }
    public bool IsActive { get; set; }
    public virtual ICollection<Section> Sections { get; set; }
}

public class Section {
    public int Id { get; set; }
    public int Name { get; set; }
    public int GroupId { get; set; }
    public Group Group { get; set; }
    public bool IsActive { get; set; }
    public virtual ICollection<Course> Courses { get; set; }
}

In method syntax, the query would probably look something like this:

var results = db.Group
    .Where(g => g.IsActive)
    .GroupJoin(
        db.Section.Where(s => s.IsActive),
        g => g.Id,
        s => s.GroupId,
        (g, s) => new
        {
            Group = g,
            UserSections = s
                .GroupJoin(
                    db.Course.Where(c => c.IsActive && c.UserId == _userId).DefaultIfEmpty(),
                    ss => ss.Id,
                    cc => cc.SectionId,
                    (ss, cc) => new
                    {
                        Section = ss,
                        UserCourses = cc
                    }
                )
        })
    .ToList();

And you would consume the result as:

foreach (var result in results)
{
    var group = result.Group;

    foreach (var userSection in result.UserSections)
    {
        var section = userSection.Section;

        var userCourses = userSection.UserCourses;

    }
}

Now, if you don't need additional filtering of the group results on database level, you can as well go for the INNER JOIN and LEFT OUTER JOIN approach by using this LINQ query and do the grouping in-memory:

var results = db.Group
    .Where(g => g.IsActive)
    .Join(
        db.Section.Where(s => s.IsActive),
        g => g.Id,
        s => s.GroupId,
        (g, s) => new
        {
            Group = g,
            UserSection = new
            {
                Section = s,
                UserCourses = db.Course.Where(c => c.IsActive && c.UserId == _userId && c.SectionId == s.Id).DefaultIfEmpty()
            }
        })
    .ToList() // Data gets fetched from database at this point
    .GroupBy(x => x.Group) // In-memory grouping
    .Select(x => new
    {
        Group = x.Key,
        UserSections = x.Select(us => new
        {
            Section = us.UserSection,
            UserCourses = us.UserSection.UserCourses
        })
    });

Remember, whenever you're trying to access group.Sections or section.Courses, you will trigger the lazy loading which will fetch all child section or courses, regardless of _userId.

SQL to LINQ - Left Join Before Inner Join

Use:

var query = from m in tableA
join s in tableB on m.ShipId equals s.ShipId
join p in tableC on s.PostageId equals p.PostageId
where m.MailId == "Specification" && p.PostageCode == "Package"
select m.MailId;

Your query uses a LEFT OUTER JOIN but it doesn't need it.

It will, in practice, function as an INNER JOIN due to your tc.PostageCode = 'Package' clause. If you compare to a column value in a table in a WHERE clause (and there are no OR clauses and you aren't comparing to NULL) then effectively all joins to get to that table will be treated as INNER).

That clause will never be true if TableB is null (which is why you use LEFT OUTER JOIN vs INNER JOIN) - so you should just use an INNER JOIN to make the problem simpler.

How to do Join in Linq query on column A or column B

INNER JOIN is a more readable syntax, but it can be translated, in pure SQL, into cartesian product of the tables filtered with a WHERE clause.

So we can simply use a WHERE clause and build the linq from that:

from x in Foo
from y in Bar
    .Where(y => y.field1 == x.field1 || y.field2 == x.field2)

Ps: INNER JOIN and WHERE are not evaluated at the same time in SQL so they are not equivalent. ON condition filter the INNER JOIN then the WHERE is apply. But even they will have the same result but not the same execution plan depending on the Database of course.

What Is the Syntax For an Inner Join in Linq to Sql