LINQ - GridView rows data from different tables

Asked By Nirmala on 23-Jan-12 01:01 PM
Hello,

I have a gridview in that i want to display the data of two tables.

One table data in first row and another table data in second row. But when i am using join it is showing multiple data as
Number is not a primary key:

table 1
tbl1_ID Name Number
1 ABC 121212
2 DEF 232323
table 2
tbl2_ID Name Number
4 STU 121212
5 VWX 232323
I want to display the data in gridview like:
tbl2_ID Name Number
1 STU 121212
2 ABC 121212


kalpana aparnathi replied to Nirmala on 23-Jan-12 01:13 PM
private IQueryable<LocalEmployee> FindEmployeeByName(string Name)
 
{
 
  EmployeeDataContext db = new EmployeeDataContext();
 
 
 
   return from table1 in db.Employees
 
       where table1.Name == Name
 
       orderby table1.Name
 
       select new table2
 
       {
 
         tbl2_ID=table1.tbl2_ID,
 
        
 
         Name = table2.Name,
 
      
 
       };
 
}
[)ia6l0 iii replied to Nirmala on 23-Jan-12 08:39 PM
Not sure if you want only the "filtered" rows based on the Number. If yes, then do a JOIN using linQ. 

var joinQuery =
        from t1 in tbl1.AsEnumerable() join t2 in tbl2.AsEnumerable()
        on t1.pk equals t2.pk into anont3
        select new
        {
            tbl1_ID = t1.tbl1_ID,
            Name= t1.Name,
            Number= t1.Number
        };

GridView1.DataSource = joinQuery;
GridView1.DataBind();

Otherwise, you can simple do a UNION and then a ORDER BY.

var coMingledQuery =
((from t1 in tbl1.AsEnumerable() select t1)
.Union
        (from t2 in tbl2.AsEnumerable() select t2))
.orderby(t1["Number"])


Today has been a long day. So please expect syntax errors :)

Hope this helps.