2

I have two tables in my database that look like that:

Customer:

C_ID  city
--------------
1     Dhaka
2     New york
3     London

Personal_Info:

P_ID  C_ID  Field       value
-------------------------------
1     1     First Name  Nasir
2     1     Last Name   Uddin
3     2     First Name  Jon
4     3     First Name  Lee


I need a select result like that:

C_ID = '1':

C_ID  Name (First Name + Last Name)  City
------------------------------------------
1     Nasir Uddin                    Dhaka

C_ID = '2':

C_ID  Name (First Name + Last Name)  City
---------------------------------------------
2     Jon                            New york

How would the corresponding Linq query look like?

Thanks
Nahid

Thunraz
  • 570
  • 5
  • 18
Md Nasir Uddin
  • 2,130
  • 8
  • 38
  • 59
  • See the answer for http://stackoverflow.com/questions/1122942/linq-to-sql-left-outer-join-with-multiple-join-conditions – Miika L. Apr 05 '12 at 09:27
  • 4
    After almost 50 questions you should start using the code block button in the editor. – juergen d Apr 05 '12 at 09:27

1 Answers1

3

Following a previous answer such as Linq to Sql: Multiple left outer joins you can see the structure for solving this eg something like: 

var result = from customer in customers  
                   from personalFirst in personal  
                       .Where(pf => pf.Field == "First Name" && pf.C_ID == customer.C_ID)  
                       .DefaultIfEmpty() 
                   from personalLast in personal  
                       .Where(pl => pl.Field == "Last Name" && pl.C_ID == customer.C_ID)  
                       .DefaultIfEmpty()  
                    where customer.C_ID == 2  
                    select new { customer.C_ID, Name = (personalFirst != null ? personalFirst.Value : "") + " " + (personalLast != null ? personalLast.Value : "") };

Obviously if you want all records then remove the restriction on C_ID = 2

Community
  • 1
  • 1
kaj
  • 5,133
  • 2
  • 21
  • 18
  • 2
    Could the downvoter please explain why? The LINQ works so therefore answers the question. If there's a better approach (and there may be) then its worth showing – kaj Apr 05 '12 at 10:48