LINQ (C#) Join - Displaying duplicated data in single row

Question

I'm new to LINQ. I am trying to join a table and view and display the result in data table.

Consider my scenario (Database) :

My requirement is to display as follows on UI in DataTable as follows :

I had tried the following left-outer join (sample code) : `

var query = (

 from request in DB.Request
 join financeaprv in DB.DemoView on request.SurrogateID equals financeaprv.SurrogateID into financeaprvList
                             from financeaprv in financeaprvList.DefaultIfEmpty()
                             where financeaprv.ApproverType=="Finance"

  join sysadmin in DB.DemoView on request.SurrogateID equals sysadmin.SurrogateID into sysadminList
                             from sysadmin in legalaprvList.DefaultIfEmpty()
                             where sysadmin.ApproverType=="Admin"

  select new queryResult                             
  {
        FinanceApprv = financeaprv.ApproverName
        Admin = sysadmin.ApproverName

  }

`

But it was unsuccessful. Can anyone tell me what would be the LINQ query for my problem.. Thank you in advance.

You don't need to use left join just use join only, by default linq will consider this as inner join and you can achieve this by inner join only. See this http://stackoverflow.com/questions/3217669/how-to-do-a-join-in-linq-to-sql-with-method-syntax — Bharat, Nov 29 '16 at 11:04
Tried it. But it's not returning all rows. I think my problem would require subqueries+join. — Mr.Human, Nov 29 '16 at 11:49
This is called *pivoting* and there are many questions about it. Maybe you can find one that suits you. — Gert Arnold, Nov 29 '16 at 13:30

score 0 · Answer 1 · answered Nov 29 '16 at 12:52

So you want to combine the Request table and the DemoView table on matching SurrogateId and you want as result the columns RequestId, SurrogateId, Finance, Manager and Admin.

Your LINQ sequence would be:

Request.Join(DemoView,     // join the Request and DemoView tables
    r => r.SurrogateId,    // from each Request element take the SurrogateId
    d => d.SurrogateId,    // from each DemoView element that the SurrogateId
    (req, dem) => new                  // when they match, take the request element
    {                                  // and the demoView element
        RequestId = req.RequestId,     // and create a new object containing
        SurrogateId = req.SurrogateId, // the mentioned properties
        Finance = dem.Finance,
        Manager = dem.Manager,
        Admin = dem.Admin,
    });

Thanks for the reply @Harald Coppoolse. The solution you've provided is in Method syntax. I tried to convert it in Query syntax and I'm half-way stuck. But I'm sure the following part is correct : **`var query = from request in DB.Request join appr in DB.DemoView on request.SurrogateId equals appr.SurrogateId into select new SearchResult { }`** — Mr.Human, Nov 29 '16 at 19:06

J-Mean · Answer 2 · 2016-11-30T07:03:59.353

Below is working please try it.

class Program
{
    static void Main(string[] args)
    {
        List<Request> lstRequest = new List<Request>();
        lstRequest.Add(new Request(1, 1));
        lstRequest.Add(new Request(2, 2));


        List<DemoView> lstDemoView = new List<DemoView>();
        lstDemoView.Add(new DemoView(1, "Ram", "Finance"));
        lstDemoView.Add(new DemoView(1, "Sam", "Manager"));
        lstDemoView.Add(new DemoView(1, "Dan", "Admin"));
        lstDemoView.Add(new DemoView(2, "abc", "Finance"));



        var query = (from request in lstRequest 
                     select new
                     {
                         RequestID = request.RequestID,
                         SurrogateID = request.SurrogateID,
                         Finance = lstDemoView.Any(x => x.SurrogateID == request.SurrogateID && x.ApproverType.ToLower() == "finance") ? lstDemoView.SingleOrDefault(x => x.SurrogateID == request.SurrogateID && x.ApproverType.ToLower() == "finance").ApproverName : "",
                         Manager = lstDemoView.Any(x => x.SurrogateID == request.SurrogateID && x.ApproverType.ToLower() == "manager") ? lstDemoView.SingleOrDefault(x => x.SurrogateID == request.SurrogateID && x.ApproverType.ToLower() == "manager").ApproverName : "",
                         Admin = lstDemoView.Any(x => x.SurrogateID == request.SurrogateID && x.ApproverType.ToLower() == "admin") ? lstDemoView.SingleOrDefault(x => x.SurrogateID == request.SurrogateID && x.ApproverType.ToLower() == "admin").ApproverName : ""
                     }).ToList();
    }
}

public class Request
{
    public int RequestID { get; set; }
    public int SurrogateID { get; set; }

    public Request(int RequestID, int SurrogateID)
    {
        this.RequestID = RequestID;
        this.SurrogateID = SurrogateID;
    }

}

public class DemoView
{
    public int SurrogateID { get; set; }
    public string ApproverName { get; set; }
    public string ApproverType { get; set; }

    public DemoView(int SurrogateID, string ApproverName, string ApproverType)
    {
        this.SurrogateID = SurrogateID;
        this.ApproverName = ApproverName;
        this.ApproverType = ApproverType;
    }
}

Note:- Don't Use DB.DemoView multiple time in query. Just once collect in list object and Use that list in query. So multi database call is saved.

Thanks for the answer @J-Mean . If I'm not wrong we would have to convert the DemoVIew to list string as follows : List lstDemoView = DB.DemoView.ToList() or else it would throw an error. — Mr.Human, Nov 29 '16 at 18:17
Thanks for pointing out issues with multiple DB call in my query. Just one help - can you also provide how the query would look like in the manner I've written (i.e. multiple DB calls) . — Mr.Human, Nov 29 '16 at 18:27
I am sorry I didn't get your last comment. Please can you explain it. — J-Mean, Nov 29 '16 at 20:58
It means what would be the query if I made the join ( hence multiple DB calls) for each of the values( i.e Finance, Manager). — Mr.Human, Nov 30 '16 at 04:22
Like I join Demoview and Request table every single time for finance,admin etc.... I know it's not good for performance but I would like to know how the query would be — Mr.Human, Nov 30 '16 at 04:33
For e.g.) `var query = ( from request in DB.Request join financeaprv in DB.DemoView ...........` — Mr.Human, Nov 30 '16 at 05:24
To meet your requirement we don't require join because in your case we are converting row to columns (i.e. ApproverType)in Output. Join is required when merging 2 tables columns. I have pivot the hierarchy back into a row/column form to get expected output. Had simulated your scenario and created demo and able to get same output. Please find above updated solution — J-Mean, Nov 30 '16 at 06:54
Ok. Thanks to pint out that join is not required. Big thanks for your help — Mr.Human, Nov 30 '16 at 10:07

Mr.Human · Accepted Answer · 2016-12-06T11:40:09.893

I came up with the following query and it works perfectly well :

var query = (from request in DB.Request let approvers = DB.DemoView.Where(sid => sid.SurrogateID == request.SurrogateID).Select(col => new { col.ApproverName, col.ApproverType}) select new queryResult{FinanceApprover = approvers.Where(state => state.ApproverType.Contains("Finance")).Select(name => name.ApproverName).FirstOrDefault(),//and so on}

I have used the .Contains() on purpose as per my requirement. .Equals() will do just fine.

LINQ (C#) Join - Displaying duplicated data in single row

3 Answers3