Linq provides developers the ability to query list objects. It is powerful tool and support the joins on two different list objects. Common scenario is the Master-Detail relationship or you can say Parent Child relationship, where one list is linked with another list using some common field.

Say for instant I have a list of Orders. Each order must be mapped with particular client. I have another collection say Client and I want to display the client summary report which show me how many orders a particular client has placed.

We can solve this problem by using Join keyword available for Linq as mentioned below.

I have used Visual Studio 2015 and used default MVC template. Open the HomeController.cs file and type in following code.

   
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Linq.Controllers
{
    public class HomeController : Controller
    {
        public class Client
        {
            public int id { get; set; }
            public string name { get; set; }
        }
        public class Order
        {
            public int Cid { get; set; }
            public int Oid { get; set; }
            public float amount { get; set; }
        }

        public class ClientOrder
        {
            public string ClientName { get; set; }
            public int TotalNoOfOrder { get; set; }
            
        }
      
        public ActionResult Index()
        {

            List<Client> clientList = new List<Client>();
            List<Order> orderList = new List<Order>();

            for (var i = 0; i < 5; i++)
            {
                clientList.Add(new Client { id = i, name = "client " + i.ToString() });
                orderList.Add(new Order { Cid = i, Oid = i, amount = i * 1000 });
            }

            orderList.Add(new Order { Cid=1, Oid=6, amount=3500});
            orderList.Add(new Order { Cid = 4, Oid = 7, amount = 3400 });

            var premiumClient = clientList.Where(x => x.id > 3); // using  lambda expression
            var filteredOrderList = orderList.Where(x => x.Oid > 3); // using  lambda expression

            var simpleQuery = from client in clientList
                              where client.id > 3
                              select client;


            var masterDetailQuery = from myclient in clientList
                                    join myorder in orderList
                                    on myclient.id equals myorder.Cid into ords
                                    select new ClientOrder
                                    {
                                        ClientName = myclient.name,
                                        TotalNoOfOrder= ords.Count()
                                    };

            string goodclient = string.Empty;
            foreach (var i in masterDetailQuery)
            {
                goodclient = goodclient + "   " + i.ClientName + "   " + i.TotalNoOfOrder.ToString();

            }
            ViewBag.ClientOrder = masterDetailQuery;
            return View();

        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

Save the above file and change the Index.cshtml file as mentioned as below.

   
@{
      ViewBag.Title = "Home Page";
}

<section class="container col-lg-12">

    <div class="table-responsive">
        <table class="table table-striped custom-grid">
            <thead>
                <tr>
                    <th>Client Name</th>
                    <th>No of Order Placed</th>                 
                </tr>
            </thead>
            <tbody>

                @foreach (var user in ViewBag.ClientOrder)
                {
                    <tr>
                        <td><strong>@user.ClientName</strong></td>
                        <td><strong>@user.TotalNoOfOrder</strong></td>
                     
                    </tr>
                }
            </tbody>

        </table>
    </div>

</section>



Output
Output-Join-Linq

Source Code



You can download the full source code at https://github.com/amitntrivedi/article/tree/master/Linq

Please follow and like us:
LINQ query for Master Detail Page

One thought on “LINQ query for Master Detail Page

  • June 26, 2016 at 6:51 pm
    Permalink

    Great Article! Thanks for sharing 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *