How to select with linq to entity Categories and SubCategories?

Here is the structure of my Categories table

Categories Table
--------------------------------
ID      Name      ParentID

1       NameOne    NULL
2       NameTwo    1
3       NameThree  1
4       NameFour   1

Here is my ItemTable that has reference key to category table

Items Table
--------------------------------------------
ItemID   CategoryID SubCategoryID      ItemName
1        1          2                  ItemOne
2        1          3                  ItemTwo

How can I join it to get unique records like in example below, using LINQ to entity?

The result has to be

ItemID               Cate开发者_运维问答goryName   SubCategoryName           ItemName
-----------------------------------------------------------------------
1                    NameOne        NameTwo                   ItemOne
2                    NameOne        NameThree                 ItemTwo   

I'm assuming you are using entity data model (.edmx file) where you dragged all the necessary tables from the database.

It is hard for me to give you an exact query since I don't know what you have named certain references and models.

Hope this helps you to start:

var q = from item in context.Items
        select new {
            ItemID = item.ItemID,
            CategoryName = item.Category.Name,
            SubCategoryName = item.SubCategory.Name,
            ItemName = item.ItemName
        };

Please note that "Category" and SubCategory" are references to the entries in Categories table by CategoryId and SubCategoryId in Items table. So replace this to what you have named them in your data model file.

Good luck!

Something like this might work depending on how you have your entities defined.

var results = Entities.Items.Select( i => new { ItemID = i.ID, CategoryName = i.Category.Name, SubCategoryName = i.SubCategory.Name, ItemName = i.Name } );

I agree with @Mouhannad's assumption:

I'm assuming you are using entity data model (.edmx file) where you dragged all the necessary tables from the database.

However, if it is not right, then you could try to use join, like this:

List<Item> items = someMethodToFillItems();
List<Category> categories = someMethodToFillCategories();

var q = (from item in items join
          cat in categories on cat.Id equals item.CategoryId
          subCat in categories on subCat.ParentId equals cat.Id
    select new {
        ItemID = item.ItemID,
        CategoryName = cat.Name,
        SubCategoryName = subCat.Name,
        ItemName = item.ItemName
    }
);