CODEWITHSUNDEEP
c#sqllinq
Bautzi89
Bautzi89

Reputation: 346

Translate SQL statement with inner joins to LINQ

I have to make some queries in C# using LINQ. I tried my logic with a SQL request. It looks like this:

SELECT DISTINCT test_laktationen.lom, test_laktationen.laktanfang, test_laktationen.laktende, b_milch_hms_diagnose.diagnose
FROM            test_laktationen INNER JOIN
                         b_milch_hms_diagnose ON test_laktationen.lom = b_milch_hms_diagnose.lom AND b_milch_hms_diagnose.behanddatum BETWEEN 
                         test_laktationen.laktanfang AND test_laktationen.laktende INNER JOIN
                         b_milch_hms_kalbung ON test_laktationen.lom = b_milch_hms_kalbung.lom AND test_laktationen.laktanfang = b_milch_hms_kalbung.kalbedatum
WHERE        (b_milch_hms_diagnose.diagnose LIKE '6.01.01.%' OR
                         b_milch_hms_diagnose.diagnose LIKE '6.01.02.%' OR
                         b_milch_hms_diagnose.diagnose LIKE '6.01.03.%' OR
                         b_milch_hms_diagnose.diagnose LIKE '6.01.04.%') AND (b_milch_hms_kalbung.gebverlauf = 3 OR
                         b_milch_hms_kalbung.gebverlauf = 4)

This works like it should, but now I have to make this request from data tables. Is this possible with LINQ? What would be good practice to solve this problem?

Upvotes: 0

Views: 141

Answers (1)

Joel Etherton
Joel Etherton

Reputation: 37543

You would need a data context to perform direct access. I would recommend Linq2Sql classes file (*.dbml) to get it started. Once you create your data structure in the Linq2Sql class with the server explorer, your linq statement would look something like:

var context = new MyLinq2SqlContext();

var results = (from lak in context.test_laktationen
    join milch in context.b_milch_hms_diagnose
        on lak.lom equals milch.lom
    join kalbung in context.b_milch_hms_kalbung 
        on lak.lom equals kalbung.lom and lak.laktanfang equals kalbung.kalbedatum
    where 
        (milch.diagnose.StartsWith("6.01.01") ||
        milch.diagnose.StartsWith("6.01.02") ||
        milch.diagnose.StartsWith("6.01.03") ||
        milch.diagnose.StartsWith("6.01.04")) &&
       (kalbung.gebverlaug == 3 || kalbung.geberlauf == 4) &&
       ((milch.behanddatum > lak.laktanfang && milch.behanddatum < lak.laktende) ||     (milch.behanddatum < lak.laktanfang && milch.behanddatum > lak.laktende))
    select new {
            lak.lom, 
            lak.laktanfang,
            lak.laktende,
            milch.diagnose
    }).Distinct();

This will probably require tweaking in the final "where" clause to make sure you get the "between" portion correct. BETWEEN doesn't translate natively into from Linq, but it can be coded in as long as you can predict which value is likely to be greater.

Upvotes: 1

Related Questions