Convert SQL query to LINQ

Question

I have a beginners LINQ2SQL question. I have this huge (but not complex) SQL statement:

SELECT Artikel.ArtikelID, 
       Artikel.CategorieID, 
       Artikel.ImageFile, 
       Artikel.RetailPrijs, 
       ISNULL(ShopArtikel.VerkoopsPrijs, Artikel.VerkoopsPrijs) AS VerkoopsPrijs, 
       Artikel.ArtikelCode, 
       Artikel.InAssortimentSinds, 
       ArtikelTaal.ArtikelNaam, 
       ArtikelTaal.ArtikelOmschrijving
FROM   Artikel 
INNER JOIN ArtikelTaal ON Artikel.ArtikelID = ArtikelTaal.ArtikelID 
INNER JOIN ShopArtikel ON Artikel.ArtikelID = ShopArtikel.ArtikelID 
INNER JOIN Categorie ON Artikel.CategorieID = Categorie.CategorieID 
INNER JOIN CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID 
INNER JOIN Shop ON ShopArtikel.ShopId = Shop.ShopID 
INNER JOIN CategorieGroepShop ON Shop.ShopID = CategorieGroepShop.ShopId 
INNER JOIN Taal ON ArtikelTaal.TaalCode = Taal.TaalCode AND CategorieTaal.TaalCode = Taal.TaalCode 
INNER JOIN CategorieGroepTaal ON Taal.TaalCode = CategorieGroepTaal.TaalCode AND CategorieGroepShop.CategorieGroepId = CategorieGroepTaal.CategorieGroepID 
INNER JOIN CategorieGroep ON Categorie.CategorieGroepID = CategorieGroep.CategorieGroepID AND CategorieGroepTaal.CategorieGroepID = CategorieGroep.CategorieGroepID AND CategorieGroepShop.CategorieGroepId = CategorieGroep.CategorieGroepID
WHERE (Shop.ShopID = 23) AND 
      (Taal.TaalCode = 'dut') AND 
      (Artikel.Onzichtbaar = 0) AND 
      (Artikel.NietBestelbaar = 0) AND 
      (Categorie.Onzichtbaar = 0) AND 
      (Artikel.Voorraad >= Artikel.LevertijdDrempel)

and I am converting this to LINQ and have this:

var allProducts = from artikelen in dc.Artikels
join sa in dc.ShopArtikels on artikelen.ArtikelID equals sa.ArtikelID
join at in dc.ArtikelTaals on artikelen.ArtikelID equals at.ArtikelID
join cat in dc.Categories on artikelen.CategorieID equals cat.CategorieID
join catt in dc.CategorieTaals on cat.CategorieID equals catt.CategorieID
join catg in dc.CategorieGroeps on cat.CategorieGroepID equals catg.CategorieGroepID
join catgt in dc.CategorieGroepTaals on catg.CategorieGroepID equals catgt.CategorieGroepID
join sh in dc.Shops on sa.ShopId equals sh.ShopID
join catgs in dc.CategorieGroepShops on sh.ShopID equals catgs.ShopId
join tl in dc.Taals on new { tc1 = at.TaalCode, tc2 = catgt.TaalCode } equals new { tc1 = tl.TaalCode, tc2 = tl.TaalCode } 
where sh.ShopID == shop.BLL.Business.ShopController.CurrentShop.Id
select dc.Artikels;

but I have the idea that I made some (minor) mistakes while joining. any ideas please!

EDIT I have rewritten the LINQ query to this:

var allProducts = from artikelen in dc.Artikels
join at in dc.ArtikelTaals on artikelen.ArtikelID equals at.ArtikelID
join sa in dc.ShopArtikels on artikelen.ArtikelID equals sa.ArtikelID
join cat in dc.Categories on artikelen.CategorieID equals cat.CategorieID
join catt in dc.CategorieTaals on cat.CategorieID equals catt.CategorieID
join sh in dc.Shops on sa.ShopId equals sh.ShopID
join catgs in dc.CategorieGroepShops on sh.ShopID equals catgs.ShopId
join tl in dc.Taals on new { tc1 = at.TaalCode, tc2 = catt.TaalCode } equals new { tc1 = tl.TaalCode, tc2 = tl.TaalCode } 
join catgt in dc.CategorieGroepTaals on new { tl.TaalCode, catgs.CategorieGroepId } equals new { catgt.TaalCode, catgt.CategorieGroepID }
join catg in dc.CategorieGroeps on new { cat.CategorieGroepID, catgt.CategorieGroepID, catgs.CategorieGroepId } equals new { catg.CategorieGroepID, catg.CategorieGroepID, catg.CategorieGroepID }
where sh.ShopID == 230
select dc.Artikels;

but I have a syntax error after "dut" } Edit 2: changed the join and replaced "dut" with the corresponding field in the database. still have the error after the first } it says: type inference failed in the call to 'Join'

Please elaborate on the question. What problems are you having? Any errors? If not, what aren't you happy with? — Rushyo, Sep 17 '10 at 13:49
Also, I would definitely describe this as 'complex'. http://en.wiktionary.org/wiki/complex#Adjective — Rushyo, Sep 17 '10 at 13:50
What makes you think that? Have you run it to see if it works? — Iain Ward, Sep 17 '10 at 13:51
To start with you might want to use http://www.linqpad.net... — Yves M., Sep 17 '10 at 13:51
possible duplicate of [SQL to LINQ Tool](http://stackoverflow.com/questions/296972/sql-to-linq-tool) — Filip Ekberg, Sep 17 '10 at 13:53
Hi Rushyo, I think JP is asking if the LINQ query can be written in better way — muek, Sep 17 '10 at 13:53
I have linqpad, but don't like that i have to re-write it because it doesn't know my datacontext. and it runs but gives wierd results. i don't know what i have done wrong. — JP Hellemons, Sep 17 '10 at 13:54
sheesh, dude - just create a view, then you can query straight into that using LINQ. — RPM1984, Sep 20 '10 at 03:45
Linqpad can know about your data context, if you learn how to use it. You can link to your assembly and it will know everything your dc does. Just consider that linqpad runs in the context of your data context, so i usually have a `var dc = this` at the top, then i'm good. — Erik Funkenbusch, Sep 20 '10 at 04:07
@RPM1984 I don't want a view, I just want to get this version to work ;) — JP Hellemons, Sep 20 '10 at 07:59

score 2 · Accepted Answer · answered Sep 17 '10 at 13:51

2

Some of the SQL joins have multiple join conditions, which you didn't put in the LINQ query.

answered Sep 17 '10 at 13:51

Joachim VR

2,320
1
15
24

ok thanks, will look into that. haven't noticed it because my eyes are getting square shapes this friday – JP Hellemons Sep 17 '10 at 13:54
I would only put database fields in the join condition. Other comparisons with supplied values (like "dut") should be placed in the where clause. – Joachim VR Sep 17 '10 at 15:03

score 1 · Answer 2 · answered Sep 20 '10 at 03:42

1

If this is something that will be frequently run then you should rewrite it as a stored procedure. I believe it is too convoluted and complex for a LINQ statement - too hard to see what's going on.

answered Sep 20 '10 at 03:42

Kirk Broadhurst

27,836
16
104
169

score 0 · Answer 3 · answered Sep 17 '10 at 13:52

0

There is a tool for it, but I didn't try it. May be it's usefull for you.

http://www.sqltolinq.com/

answered Sep 17 '10 at 13:52

mavera

3,171
7
45
58

For straight forward queries like these that's probably a good idea, but eventually it's more useful to understand what kind of SQL LINQ generates. I've seen some weird stuff happening there. – Joachim VR Sep 17 '10 at 13:58

score 0 · Answer 4 · answered Sep 20 '10 at 03:22

It looks like the error line is actually a "Where" cause but not "Joining". You can actually split the whole long Linq statement into smaller Query.

so for this case, its better to split it like this:

var at = from a in dc.ArtikelTaals
           where a.TaalCode == "dut"
           select a;

var catt = from c in dc.CategorieTaals
           where c.TaalCode == "dut"
           select c;

.....

and you can join the IQueryable "at" and "catt" in your complex query later.

Convert SQL query to LINQ

4 Answers4