score:1

Accepted answer

Try this:

(from cat in cats
join res in resources on cat.ResId equals res.Id let categoryName = res.Text
join cat1 in cats on cat.ParentId equals cat1.Id into parentJoin
from pj in parentJoin.DefaultIfEmpty() let parentCatResId =pj==null?0: pj.ResId
join res1 in resources on parentCatResId equals res1.Id into resJoin
from res2 in resJoin.DefaultIfEmpty() let parentName = (res2==null?string.Empty:res2.Text)
    select new CategoryVM
    {
        Id = cat.Id,
        ResId = cat.ResId,
        CatName = categoryName,
        ParentId = cat.ParentId,
        ParentName = parentName
    }).ToList();

score:0

Say you have the following data with your tables

dbo.Categories

ID  CategoryNameResID   ParentCategory
1   1                   NULL
2   2                   NULL
3   3                   1
4   4                   NULL
5   5                   4
6   6                   4
7   7                   4


dbo.Resources

ID  Text                Lang
1   Standard            en-GB
2   Custom              en-GB
3   Standard Oversize   en-GB
4   Loose               en-GB
5   Loose 2F Set        en-GB
6   Loose (4” Scale)    en-GB
7   Loose (6” Scale)    en-GB

The following LINQ statements will output the desired results:

public class CategoryViewModel
{
    public int ID { get; set; }
    public int CategoryNameResID { get; set; }
    public string CategoryName { get; set; }

    public int? ParentCategory { get; set; }
    public string ParentCategoryName { get; set; }
}   


var categories = (from cat in Categories
                  join res in Resources on cat.CategoryNameResID equals res.ID let categoryName = res.Text
                  select new CategoryViewModel
                  {
                    ID = cat.ID,
                    CategoryNameResID = cat.CategoryNameResID,
                    CategoryName = categoryName,
                    ParentCategory = cat.ParentCategory,    
                    ParentCategoryName = Resources.FirstOrDefault(r => r.ID == cat.ParentCategory).Text
                  }).ToList();


foreach(var c in categories)
{
   Console.WriteLine(c.CategoryName + " - " + c.ParentCategoryName);   
}

// Prints
Standard - 
Custom - 
Standard Oversize - Standard
Loose - 
Loose 2F Set - Loose
Loose (4” Scale) - Loose
Loose (6” Scale) - Loose

Related Articles