CODEWITHSUNDEEP
linqasp.net-mvc-4
Niranjan Godbole
Niranjan Godbole

Reputation: 2175

Linq query to get data from Database using group by clause

I am developing MVC4 application. I ended up with one complex scenario. I have following table in sql.

upld_Id     Label         Value          Commited
1000        DocNumber     123            0
1000        ExpiryDate    01/01/2016     0
1000        Docnumber     456            1
1000        ExpiryDate    01/01/2018     1

I am supposed to get output in the below format.

upld_Id     Label         OldValue      NewValue 
1000        Docnumber     123           456
1000        ExpiryDate    01/01/2016    01/01/2018

I tried as below.

var logDetails = (from c in db.logTable
                  join tbl in db.MetaTable on c.id equals tbl.id
                  //GroupBy
                  where  (c.commited==false || c.commited==true) 
                  select new ClassName
                  {
                      //Get Values
                  }).toList();

This is what i tried in LINQ. I am not sure how to apply LINQ here

var logDetails = (from c in db.ts_upldlog_content
                                      join tbl in db.ts_upld_doc on c.upld_docid equals tbl.upld_docid
                                      join doc in db.tm_doc_type on tbl.upld_doctypeid equals doc.doc_typeid
                                      where c.upld_docid==data
                                 group c by c.upld_contentlabel into grouping
                                 select new logdetails
                                 {
                                    updatedOn=c.updatedOn //Here Error
                                    contentLabel=grouping.Key,
                                    oldValue = grouping.FirstOrDefault(x => x.commited ==false).upld_contentvalue,
                                    newValue = grouping.FirstOrDefault(x => x.commited == true).upld_contentvalue,
                                 }).ToList();

Can someone tell me is this possible in LINQ? Thanks

Upvotes: 1

Views: 813

Answers (3)

Gilad Green
Gilad Green

Reputation: 37299

You need to group your results by the Label and then for each of the oldValue and newValue retrieve an item that matches the predicate (commited == 1 / commited == 0):

var logDetails = (from c in db.ts_upldlog_content
                  join tbl in db.ts_upld_doc on c.upld_docid equals tbl.upld_docid
                  join doc in db.tm_doc_type on tbl.upld_doctypeid equals doc.doc_typeid
                  where c.upld_docid == data
                  group c by new { c.upld_contentlabel, c.updateOn } into grouping
                  select new logdetails
                  {
                      updatedOn = c.Key.updateOn,
                      contentLabel = grouping.Key.upld_contentlabel,
                      oldValue = grouping.FirstOrDefault(x => x.commited ==false).upld_contentvalue,
                      newValue = grouping.FirstOrDefault(x => x.commited == true).upld_contentvalue,
                  }).ToList();

Upvotes: 1

Fredrik
Fredrik

Reputation: 2317

I would GroupBy (upld_Id, Label) in a new anonymous type...

var query = db.logTable.GroupBy(l => new { l.upld_Id, l.Label })
                       .Select(g => new
                       {
                           upld_Id = g.Key.upld_Id,
                           Label = g.Key.Label,
                           OldValue = g.FirstOrDefault(o => o.Commited == 0).Value, 
                           NewValue = g.FirstOrDefault(o => o.Commited == 1).Value,
                       })
                       .ToList();

Upvotes: 1

neer
neer

Reputation: 4082

You can use a sub query for expected result:

var logDetails = (from i in db.Tbl
                  where 
                    i.Commited == false
                  select new ClassName
                  {
                    i.Id,
                    i.Label,
                    OldValue = i.Value,
                    NewValue = (db.Tbl.Where(t => t.Id == i.Id && t.Comitted == true).Select(t => t.Value).FirstOrDefault())
                  }).ToList();

Upvotes: 1

Related Questions