Left Outer join Extension method linq

Question

i would translate this queries with extension method also i would merge this queries in a single query with extension method

var variants = _ctx.Varianti.Where(i=>i.attivo==0);

var allProducts = await (from p in  _ctx.Articoli
                         where  p.cat==1
                         join v in variants on p.code equals v.code into gj
                         from articoli in gj.DefaultIfEmpty()
                         select new {
                           Codart = p.Codart,
                           Codvar = articoli.Codvar,
                        }).ToListAsync();

My classes

class Articolo{ public string Codart //key public double price }

class Variante{ public string Codart //key public string Codvar // key public int attivo }

I have to return products like so

Prod1-Variant1 Prod2-(no variant) prod3-Variant1 prod4-Variant1 prod4-Variant2 prod5-(no variant)

I should filters only variants with attivo==0 And all product without variant if they not have

The code works well but i need to optimize in single query to database and also with extension method

In T-Sql should be as so:

SELECT Codart,
       Codvar
  FROM dbo.Articoli
       LEFT OUTER JOIN dbo.Varianti
           ON dbo.Articoli.Codart = dbo.Varianti.Codart
 WHERE (Cat = 1)
   AND (attivo = 0)

I don't understand your question. Could you: Provide some example-data and example-classes? Could you rewrite your question? — kara, Mar 20 '19 at 12:34
Since `variants` is IQuerable it will be a single query to the database. Also writing it with lambda syntax rather than query syntax has no impact on performance. But if you really want to, this is how it is done: https://stackoverflow.com/a/584840/468973 — Magnus, Mar 20 '19 at 13:53
If i add in class article a navigation property to Varianti as List Varianti then can improve performance? — gigiLaTrottola, Mar 20 '19 at 13:58

kara · Accepted Answer · 2019-03-20T14:36:26.517

I'm still not sure what's the problem. Here some example how to "left-outer-join" the products and variants and select a new object.

List<Articolo> products = new List<Articolo>()
{
    new Articolo() { Code = "1", price = 1 },
    new Articolo() { Code = "2", price = 1 },
    new Articolo() { Code = "3", price = 1 },
    new Articolo() { Code = "4", price = 1 },
    new Articolo() { Code = "5", price = 1 },
};

List<Variante> variants = new List<Variante>()
{
    new Variante() { Code = "1", attivo = 0, Codvar = "v1" },
    new Variante() { Code = "3", attivo = 0, Codvar = "v1" },
    new Variante() { Code = "4", attivo = 0, Codvar = "v1" },
    new Variante() { Code = "4", attivo = 0, Codvar = "v2" },
    new Variante() { Code = "5", attivo = 1, Codvar = "v2" },
};

var result = products // Our "Left"-List 
    .GroupJoin( // Join to a "one"-to-"null or many"-List
        variants.Where(v => v.attivo == 0), // Our "right"-List
        p => p.Code, // select key in left list
        v => v.Code, // select key in right list
        (p, v) => // for every product "p" we have a list of variants "v"
            v.Any() ? // do we have vriants for our product?
                v.Select(s =>  new // for every variant we build our new product
                {
                    Code = p.Code,
                    FirstVariant = s.Codvar,
                })
                : // if we got no variants, we build a "no variant"-product
                new[] { new {
                    Code = p.Code,
                    FirstVariant = "No Variant"
                } } // here we got a list of product-variants per product ("list of lists")
            ).SelectMany(s => s); // We want one list of all product variants

foreach (var item in result)
{
    Console.WriteLine("Code: {0}, FirstVar: {1}", item.Code, item.FirstVariant);
}

in a row i should have only one product/variant ... as the sql — gigiLaTrottola, Mar 20 '19 at 14:21
@gigiLaTrottola I modified the example and added a `SelectMany()`. Now you got one result-row for every product-variant including products without variant. You could leave them out by removing the `Any()` — kara, Mar 20 '19 at 14:40
@gigiLaTrottola You're welcome. Feel free to accept and vote-up my answer :) — kara, Mar 20 '19 at 15:04
Everybody can accept answers to own questions (check-mark below the up/down-buttons). And you can up-vote. The points will be granted when you have more reputation. And you get reputation-points for accepting answers ;-) — kara, Mar 20 '19 at 15:29

Left Outer join Extension method linq

1 Answers1