CODEWITHSUNDEEP
linq-to-entitiesdevforce
Jannik
Jannik

Reputation: 2429

Join with distinct - Translating SQL to Linq Query

I am trying to translate a sql query to a working linq statement, unfortunately its not as easy as expected.

The original SQL query looks like the following:

SELECT DISTINCT tt1.ArtikelId
FROM
(
    SELECT ArtikelId FROM [dbo].[ArtSearchEinfachView]
    WHERE Feld = 'Listungsstatus' AND Wert = '0'
) tt1
INNER JOIN
(
    SELECT ArtikelId FROM [dbo].[ArtSearchEinfachView]
    WHERE Feld = 'AktiverAktikel' AND Wert = '1'
) tt2
ON tt1.ArtikelId = tt2.ArtikelId
INNER JOIN
(
    SELECT ArtikelId FROM [dbo].[ArtSearchEinfachView]
    WHERE Feld = 'Artikelbezeichnung' AND Wert like '%berentzen%'
) tt3
ON tt1.ArtikelId = tt3.ArtikelId

There will be more joins later on, will build a predicate builder later on, but for this page I only included 2.

I tried to convert it. I am using the devforce entity manager, but unfortunately its not working:

    public async Task<int> LadeArtikelCountEinfachNew(string sucheingabe)
    {
        var query = _artikelContainer.ArtSearchEinfach.Where(p => p.Feld == "Listungsart" && p.Wert == "0");

        query = query.Join(_artikelContainer.ArtSearchEinfach.Where(x => x.Feld == "AktiverAktikel" &&
                                                                         x.Wert == "1"),
                            x => x.ArtikelId,
                            y => y.ArtikelId,
                            (x,y) => y).Distinct();
        query = query.Join(_artikelContainer.ArtSearchEinfach.Where(p => p.Feld == "Artikelbezeichnung" &&
                                                                         p.Wert.Contains(sucheingabe)),
                            x => x.ArtikelId,
                            y => y.ArtikelId,
                            (x, y) => y).Distinct();

        return await query.AsScalarAsync().Count();
    }

Someone could please help me?

Upvotes: 0

Views: 104

Answers (1)

Kim Johnson
Kim Johnson

Reputation: 1227

I can't say I understand why you're using multiple inner joins on the same table instead of and/or clauses, but I'll assume there's a good reason for this. The closest analogous LINQ query for what you're doing is probably this, which uses anonymous projections:

   var query = _artikelContainer.ArtSearchEinfach
     .Where(p => p.Feld == "Listungsart" && p.Wert == "0")
     .Select(p => new { p.ArtikelId } );

   query = query.Join(_artikelContainer.ArtSearchEinfach
        .Where(x => x.Feld == "AktiverAktikel" && x.Wert == "1")
        .Select(x => new { x.ArtikelId } ),
      x => x.ArtikelId,
      y => y.ArtikelId,
      (x,y) => y);

   query = query.Join(_artikelContainer.ArtSearchEinfach
         .Where(p => p.Feld == "Artikelbezeichnung" && p.Wert.Contains(sucheingabe))
         .Select(p => new { p.ArtikelId } ),
      x => x.ArtikelId,
      y => y.ArtikelId,
      (x, y) => y);

   var results = await query.Distinct().ExecuteAsync();

Upvotes: 1

Related Questions