Devid Devid - 3 months ago 14
SQL Question

Convert SQL (Left Outer Join's) to LINQ

I am having trouble converting a Oracle Sql Query with multiple

LEFT OUTER JOIN
to LINQ. My attempts don't return the expected results. Could somebody help to convert the SQL Query below to LINQ.

string currentCulture = Culture.GetCulture();
string query = @"SELECT *
FROM CTGLBL g, CTTGLBL ct, CTLANG lang
WHERE g.sysctglbl = ct.sysctglbl(+) AND
ct.sysctlang = lang.sysctlang (+) AND
NVL(lang.activeflag, 1)= 1 AND
(ISOCODE LIKE '" + currentCulture + "%' OR ISOCODE IS NULL)";


ISOCODE belongs to CTLANG Table.

ps. I can't use tools like LINQPAD or Linqer.

Answer

But a better practice for your sql (and here converted to linq) is to use join to join tables and not the where:

string currentCulture = Culture.GetCulture();

var result = from g in CTGLBL
             join ct in CTTGLBL on g.sysctglbl equals ct.sysctglbl into ctj
             from ct in ctj.DefaultIfEmpty()
             join lang in CTLANG on ct.sysctlang equals lang.sysctlang into langj
             from lang in langj.DefaultIfEmpty()
             where (lang == null ?  1 : (lang.activeflag ?? 1)) == 1 &&
                 (lang?.ISOCODE.StartsWith(currentCulture) || lang?.ISOCODE == null)
             select new { g, ct, lang };

You can also have a "nested select" for your CTLANG like this:

string currentCulture = Culture.GetCulture();

var result = from g in CTGLBL
             join ct in CTTGLBL on g.sysctglbl equals ct.sysctglbl into ctj
             from ct in ctj.DefaultIfEmpty()
             join lang in CTTGLBL.Where(lang => lang.activeflag ?? 1 == 1 &&
                                                  (lang.ISOCODE.Contains(currentCulture) ||
                                                   lang.ISOCODE == null))
             on ct.sysctlang equals lang.sysctlang into langj
             from lang in langj.DefaultIfEmpty()
             select new { g, ct, lang };

For more explanation about joins in linq

Comments