Getting wrong data from LINQ to Entities query

I have some tables that has the following structure:

cat(
    cat_id(pk),
    cat_name)

ven(
    ven_id(pk),
    ven_name)

cat_van_rel(
    cat_ven_rel_id,
    cat_id(fk),
    ven_id(fk))

Given a ven_id passed from a function, I want the rows from cat whose id matches the cat_id in the cat_van_r开发者_C百科el table which matches the ven_id that was passed in.

Here's an example of some data:

cat:
    cat_id     cat_name
    1           food
    2           alcohol
    3           water
    4           juice


cat_ven_rel:
    cat_ven_rel - cat_id - ven_id
    1           - 1      - 1
    2           - 3      - 1
    3           - 4      - 1
    4           - 2      - 2
    5           - 1      - 2
    6           - 4      - 2

So given a ven_id 1, I should get the following rows:

1           food
3           water
4           juice

This is my query:

List<cat> lst = (from x in objEntity.cat
                 where objEntity.cat_ven_rel.Any(y => y.ven.ven_id==venid)
                 select x).ToList();

but it's returning all rows from cat table.

How can I fix this?

I Got IT :

from x in cat_ven_rel

where x.Ven.ven_id == venid

thnx for ur replies select new {x.cat.cat_name,x.cat.cat_id}

Try something like where x.cat_van_rel.ven_id == venid

(I need the generated EntityModel class to be exact)

HTH

you can't use function any here,function any is determines whether a sequence contains any elements.if the sequence contains a element it still return true! you can try:

List<cat> lst = (from x in objEntity.cat
where objEntity.cat_ven_rel.where(y => y.ven.ven_id==venid).select(y => y.cat_id).contains(x.cat_id)
select x).ToList();

the introduce of function any