locked
How to filter data from two different tables satisfying a condition? RRS feed

  • Question

  • I have two table as under:

    Table 1: BGDetails having Id as a primary key along with other fields.

    Table 2: Extension having BGId as a foreign key from the table BGDetails along with other fields.

    Here is my edmx file giving all the details:

    Since BGDate2 in the table BGDetails would be required to modify from time to time, instead of editing the record I intend it to hold as a new record in the table Extension.

    Now, I need to filter out the data from table BGDetails. The below query works fine as long as the condition to be checked pertains to the table BGDetails

     public ActionResult Index()
            {
                DateTime startDate = DateTime.Today;
                DateTime endDate = startDate.AddDays(15);
    
                var bghomedetails = db.BGDetails.Include(b => b.Bank).Include(b => b.Department)
                    .Where(b => b.BGDate2 >=startDate & b.BGDate2 <= endDate)
                    .OrderByDescending(b => b.BGDate2);
    
    
                return View(bghomedetails.ToList());
            }

    What I want is the query which also checks if the endDate is also less than the field DateTo in the table Extension after grouping records corresponding to the record in the table BGDetails since there is one-to-many relation between the two tables.

    Please help me solve the issue and do mention how to return it to the View (in MVC).

    Arun Khatri


     
    • Edited by ArunKhatri Tuesday, January 19, 2016 10:48 AM typos
    Tuesday, January 19, 2016 10:45 AM

Answers

  • Hi ArunKhatri,

    According to description, it seems a Group Multiple Tables in LINQ problem, and I made a sample as below for your reference.

    var query = from t in (from b in db.BgDetails.Include("Bank")
    
                                join c in db.Extensions
    
                                on b.Id equals c.BGId
    
                                 where b.BGDate2 >= startDate && b.BGDate2 <= endDate
    
                                 where c.DateTo <= endDate
    
                                 orderby b.BGDate2 descending
    
                                 select new { b }
    
                                 )
    
                                 group t by new { t.b.Id,t.b.BGNo,t.b.Bank,… } into g
    
                                    select new 
    
                                    {
    
                                        Id = g.Key.Id,
    
                                        BGNo = g.Key.BGNo,
    
                                        Bank = g.Key.Bank,
    
                                             …
    
                                    };
    

    Best regards,

    Cole Wu

    Wednesday, January 20, 2016 5:36 AM

All replies

  • https://msdn.microsoft.com/en-us/library/gg509017.aspx?f=255&MSPPError=-2147217396

    Linq Join with Where clause.

    Tuesday, January 19, 2016 11:12 PM
  • Hi ArunKhatri,

    According to description, it seems a Group Multiple Tables in LINQ problem, and I made a sample as below for your reference.

    var query = from t in (from b in db.BgDetails.Include("Bank")
    
                                join c in db.Extensions
    
                                on b.Id equals c.BGId
    
                                 where b.BGDate2 >= startDate && b.BGDate2 <= endDate
    
                                 where c.DateTo <= endDate
    
                                 orderby b.BGDate2 descending
    
                                 select new { b }
    
                                 )
    
                                 group t by new { t.b.Id,t.b.BGNo,t.b.Bank,… } into g
    
                                    select new 
    
                                    {
    
                                        Id = g.Key.Id,
    
                                        BGNo = g.Key.BGNo,
    
                                        Bank = g.Key.Bank,
    
                                             …
    
                                    };
    

    Best regards,

    Cole Wu

    Wednesday, January 20, 2016 5:36 AM