2

I have 2 tables to join in a query as follows:

var query = (from c in Amenites_TBLs
                 join p in AmenitesContact_TBLs on c.AmenitesCodeID
                 equals p.AmenitesCodeID
                 // group c by p.AmenitesCodeID
                 into g
                 from cc in g.DefaultIfEmpty()
                 select new
                 {
                     AmenitiesCode = Amenites_TBLs.SingleOrDefault(a => a.AmenitesCodeID == cc.AmenitesCodeID).AmenitesCode,
                     CountryCode = Amenites_TBLs.SingleOrDefault(a => a.AmenitesCodeID == cc.AmenitesCodeID).CountryCode,
                     Director = AmenitesContact_TBLs.Where(a => a.TypeOfContact.StartsWith("Dir")).FirstOrDefault(a => a.AmenitesCodeID == cc.AmenitesCodeID).ContactName});

In the Table AmenitesContact_TBLs there are just 3 records. In the table Amenites_TBLs there are 300 records but the result of the query gives only 3 records and the other 297 rows are null but the fields AmenitiesCode and CountryCode are not null in the database (they get a value).

How can I modify my query to show all 300 records?

Octahedron
  • 893
  • 2
  • 16
  • 31
JayJay
  • 1,038
  • 3
  • 25
  • 50
  • 3
    Your query is too complicated. If you have the proper relationships set up between tables, you shouldn't need all of those `SingleOrDefault` linqs. For example, looking up Director should look something like this: `Amenities.Contact.Director` – Robert Harvey Jun 26 '11 at 16:17
  • @Robert Harvey,Thanks sir,i wrote down this query because in the AmenitesContact_TBLs sometime there are 6 Contacts(records) in relationship with the PK of the Amenites_TBLs so i should show those record in the same row of a datagrid,anyway thanks so much i will try to write down it following your advice.:) – JayJay Jun 26 '11 at 16:33
  • 1
    Ah, I see. There are multiple `director` s. This might answer your question about the nulls: http://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join – Robert Harvey Jun 26 '11 at 16:36
  • @Robert Harvey,Thanks sir, i am trying to work out this step but as you told my query a bit complex so i can not reach the result yet,anyway thanks for your directory it's a good way to work out it. – JayJay Jun 26 '11 at 18:24
  • well, i tried to follow the links above but not luck, i can't show the right result. – JayJay Jun 26 '11 at 19:56

1 Answers1

0

Try this:

Amenites_TBLs.Join(AmenitesContact_TBLs , c => c.AmenitesCodeID , p => p.AmenitesCodeID,(p,o) =>
new{ AmenitiesCode = c.AmenitesCode,CountryCode = c.CountryCode,Director = p.Where(a => a.TypeOfContact.StartsWith("Dir")).ContactName });
Alaeddin Hussein
  • 738
  • 1
  • 7
  • 14