C# SQL Statement transformed TO LINQ how can i translate this statement to a working linq

Question

I am having trouble with this I have 3 Data tables i use over and over again which are cached I would like to write a LINQ statement which would do the following is this possible?

T-SQL VERSION:

SELECT P.[CID],P.[AID]
 ,B.[AID], B.[Data], B.[Status], B.[Language]
 FROM MY_TABLE_1 P
 JOIN 
  (
   SELECT A.[AID], A.[Data], A.[Status], A.[Language] FROM MY_TABLE_2 A  
   UNION ALL
   SELECT B.[AID], B.[Data], B.[Status], B.[Language] FROM MY_TABLE_3 B
  ) B on P.[AID] = B.[AID]
WHERE B.[Language] = 'EN' OR  B.[Language] = 'ANY' AND B.STATUS = 1 AND B.[Language] = 'EN' OR  B.[Language] = 'ANY' AND B.STATUS = 1

Then i would like it to create a result set of the following

Results: |CID|AID|DATA|STATUS|LANGUAGE

Answer 1

Try this:

from p in Context.MyTable1
join b in Contact.MyTable2.Concat(Contact.MyTable3)
  on p.aid equals b.aid
where b.Language == "EN" || b.Language == "ANY"
where b.Status == 1
select new
{
    p.CID,
    p.AID,
    b.Data,
    b.Status,
    b.Language
};

Answer 2

Don't do it this way.

Your two options

1. Create a view which represents your Union statement (Table2 and Table3)
2. Create a relationship on the DBML between Table1 and the new view on the AID column.
3. Do a SelectMany to get your required return result.

or (and preferred)

• Create a stored procedure that accepts the language / status (assuming they are parameters) and returns this data set. This will be the most efficient method.

You are doing database work in your business logic! Use the database for what it was intended.

Answer 3

Make sure you reference System.Data.DataSetExtensions, and use the AsEnumerable() method to use LINQ to DataSets.

            var myTable1 = new [] { 
            new { CID = "123", AID = 345, Data = 32323, Status = 1, Language = "EN"},
            new { CID = "231", AID = 123, Data = 11525, Status = 2, Language = "EN"},
            new { CID = "729", AID = 513, Data = 15121, Status = 1, Language = "ANY"},
            new { CID = "231", AID = 123, Data = 54421, Status = 2, Language = "EN"}}
            .ToDataTable().AsEnumerable();

        var myTable2 = new [] { 
            new { CID = "512", AID = 513, Data = 32323, Status = 1, Language = "ANY"},
            new { CID = "444", AID = 123, Data = 11525, Status = 2, Language = "BLAH"},
            new { CID = "222", AID = 333, Data = 15121, Status = 1, Language = "ANY"},
            new { CID = "111", AID = 345, Data = 54421, Status = 2, Language = "EN"}}
            .ToDataTable().AsEnumerable();

         var myTable3 = new [] { 
            new { CID = "888", AID = 123, Data = 32323, Status = 2, Language = "EN"},
            new { CID = "494", AID = 333, Data = 11525, Status = 1, Language = "FR"},
            new { CID = "202", AID = 513, Data = 15121, Status = 1, Language = "EN"},
            new { CID = "101", AID = 345, Data = 54421, Status = 2, Language = "ANY"}}
            .ToDataTable().AsEnumerable();

         var q = from p in myTable1
                 join b in myTable2.Union(myTable3) on p.Field<int>("AID") equals b.Field<int>("AID")
                 where (b.Field<string>("Language") == "EN" || b.Field<string>("Language") == "ANY") && b.Field<int>("Status") == 1
                 select new
                 {
                     CID = p.Field<string>("CID"),
                     B_AID = p.Field<int>("AID"),
                     P_AID = b.Field<int>("AID"),
                     Data = b.Field<int>("Data"),
                     Status = b.Field<int>("Status"),
                     Language = b.Field<string>("Language")
                 };


         var table = q.ToDataTable();

I used an extension method you can find here to test this, it's pretty useful if you are doing a lot of LINQ over DataTables.