Cannot access Table in converted query (plain SQL to LINQ)

Question

I'm trying to convert this working SQL query into a LINQ query (EF)

But in the LINQ query I can't get into the category data table. This is my working SQL Query:

SELECT cosmetics.brand, cosmetics.product, cosmetics.size, cosmetics.price, cosmetics.sale_type, cosmetics.description, cosmetics.date, cosmetics.company_id, cosmetics.category, cosmetics.[male-female], company.company_site 
FROM cosmetics INNER JOIN company ON cosmetics.company_id = company.company_id 
WHERE (cosmetics.date >= GETDATE()) 
    AND (cosmetics.[male-female] = N'female') 
    AND (cosmetics.category = N'cosmetics_perfumes') 
ORDER BY cosmetics.brand, cosmetics.product, cosmetics.size, cosmetics.price"

This is working just fine:

var female = (from c in db.cosmetics
                .Where(v => v.date > DateTime.Now)
                .Where(d => d.male_female == "female")
                .Include("company")
            select c);
return View(female.ToList());

Try using LINQPad as you will find it invaluable in a situation like this. — Kane, May 23 '11 at 09:17
What is "the category data table"? In you working sql query there are only two tables used: cosmetics and company. There seems to be no "category data table" in it. — Andrew Savinykh, May 23 '11 at 09:22

score 1 · Answer 1 · answered May 23 '11 at 09:16

1

var q =
  from cos in oc.cosmetics
  join com in oc.company on cos.company_id equals com.company_id
  where cos.date >= DateTime.Now
     && cos.male_female == "female"
     && cos.category == "cosmetics_perfumes"
  orderby cos.brand, cos.product, cos.size, cos.price
  select new { cos, com.company_site };

answered May 23 '11 at 09:16

KristoferA

12,287
1
40
62

thanks, using it i'm getting this Error in my view :The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[<>f__AnonymousType1`2[Sr_D.Models.cosmetic,System.String]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[Sr_D.Models.cosmetic]'. my view is using this model:@model IEnumerable what did i do wrong here? – ari May 23 '11 at 09:29
ok, thanks a lot, it look like this is working: var female=from c in db.cosmetics .Where(s=>s.date>DateTime.Now) .Include("company") .Where(z=>z.category=="cosmetics_perfumes").Where(x=>x.male_female=="female") select c; – ari May 23 '11 at 10:46

Cannot access Table in converted query (plain SQL to LINQ)

1 Answers1