Query data using LINQ to SQL and Entity Framework with foreign key? (Help me please)

Question

Hello!

There is a problem I need help with your query on the data using LINQ to SQL and Entity Framework (I'm using Visual Studio 2010).

My picure here: http://img.tamtay.vn/files/photo2/2010/5/28/10/962/4bff3a3b_1093f58f_untitled-1.gif

I have three tables:

1. tbl NewsDetails

2. tblNewsCategories

3. tblNewsInCategories

(See screen 1 in my picture)

Now, I want to retrieve records in the tblNewsDetails table, with condition: CategoryId=1, as the following results:

(See screen 2 in my picture)

But NewsID and CategoryId in tblNewsInCategories table is two foreign key, I do not see them and I do not know how to use them in your code.

My code has errors:

(See screen 3 in my picture)

Please help me. Thanks!

(I am a new member, should not have the right to insert images)

Answer 1

There's a couple approaches possible, here's one of em

from n in tblNewsInCategories.Include("NewsCategory").Include("NewsDetail")
where n.NewsCategory.CategoryID == 1
select n.NewsDetail

remember that n (and b, in your exemple) are TblNewsInCategories entities, which probably have the following properties :

{
    public int NewsInCategories { get; set; } // your middle table primary key
    public TblNewsCategory NewsCategory { get; set; } // a navigation property
    public TblNewsDetails NewsDetail { get; set; } // a navigation property
}

so in order to access NewsId and CategoryId, you have to go through the navigation properties.