Joining tables in LINQ/SQL

Question

I have below a collection of rows and each row consists of productid, unitid, countryid.
I need to find the details for each row in the corresponding tables (products, units, country)

for product - select product name, updatedby  
for unitid  - select unit name , updatedby  
for countryid - select countryname, uploadby

and returning the rows which has the same format

Id = product id or unitid or countryid
name = proudct name or unit name or countryname
modified = product updatedby or unit updated by or country uploadby

So, in summary -

 1. For a Collection of rows
    a. use the id to get the extra details from the respective table
    b. return the same type of collection for the results
 2. do step 1 for 
    2.a For RegularToys (Run this logic on TableBigA)
    2.b For CustomToys(Run this logic on TableB)
 3. Return all the rows 
    by adding 2.a and 2.b

How to write an sql/linq query for this? thanks

Answer 1

If I'm understanding correctly, you want to use a given ID to find either a product, a unit or a country but you're not sure which. If that's the case, then you can build out deferred queries like this to find the given entity:

var prod = from p in db.Products
           where p.ProductId = id
           select new { Id = p.ProductId, Name = p.ProductName, Modified = p.UpdatedBy };

var unit = from u in db.Units
           where p.UnitId = id
           select new { Id = u.UnitId, Name = u.UnitName, Modified = p.UpdatedBy };

var ctry = from c in db.Countries
           where c.CountryId = id
           select new { Id = c.CountryId, Name = c.CountryName, Modified = c.UploadBy };

And then execute the queries until you find an entity that matches (with ?? being the null-coalesce operator that returns the right value if the left result is null).

var res = prod.SingleOrDefault() ??
          unit.SingleOrDefault() ??
          ctry.SingleOrDefault() ??
          new { Id = id, Name = null, Modifed = null };