using multiple joins in linq to entites query

Scenario

Query with linq to entities is pretty easy until you not encounter with joins, and not only joins but multiple joins. this article will show you how you can use multiple joins in linq to entities.

SQL Script

First we write a query in sql using pubs database, to understand multiple table joins.

USE pubs
SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
   ON a.au_id = ta.au_id JOIN titles t
   ON ta.title_id = t.title_id
WHERE t.type = 'trad_cook'
ORDER BY t.title ASC

Query Result

Multiple joins in linq

Linq implementation

Now create a new Asp.net mvc project. Add Entity data model using database first approach.you can see this article for connecting database with Asp.net mvc application using Entity framework database first approach. I used PUBS database for demonstration purpose.

Now add a new class in model folder name it AuthorInfo.

Model Class AuthorInfo code

 public class AuthorInfo
    {
        public string au_lname { get; set; }
        public string au_fname { get; set; }
        public string title { get; set; }
    }

Now Add a new controller in application and write below code inside Action result method

Controller code

 pubsEntities dbcontext = new pubsEntities();
            List GetAuthorInfo=new List();
            var GetMultiJoins = (from a in dbcontext.authors
                                join ta in dbcontext.titleauthors
                                on a.au_id equals ta.au_id
                                join t in dbcontext.titles
                                on ta.title_id equals t.title_id
                                where t.type.ToUpper() == "TRAD_COOK"
                                orderby t.title1 ascending
                                select new
                                {
                                    a.au_lname,
                                    a.au_fname,
                                    t.title1
                                }).ToList();
            foreach (var item in GetMultiJoins)
            {
                GetAuthorInfo.Add(new AuthorInfo{au_lname=item.au_lname,au_fname=item.au_fname,title=item.title1});
            }
            return View(GetAuthorInfo);

I just convert sql query to linq query. first join first two tables then apply join to next one. you can apply filters usig where clause and ordering using order-by clause. then result added into authorinfo type list and return to view for rendering.

View Code

@model IEnumerable<MultipleFileUploads.Models.AuthorInfo>
@{
    ViewBag.Title = "Index";
}

<div class="col-lg-12">
    <table class="table-bordered">
        <thead>
            <tr>
                <th>Author last name</th>
                <th>Author first name</th>
                <th>Title</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@item.au_lname</td>
                    <td>@item.au_fname</td>
                    <td>@item.title</td>
                </tr>
            }
        </tbody>
    </table>
</div>

This is strongly type view based on AuthorInfo Model class.

Linq Query Result

Multiple joins in linq