Build LINQ with Sub-Select from Working SQL Query?

This SQL query works perfectly. My LINQ returns null.

[SQL]
Select c.Name, (select Name from Category Where CategoryID = e.CategorySubID)
From Category c
Join Expense e on c.CategoryID = e.CategoryID

[LINQ]
from c in context.Categories
join e in context.Expenses on c.CategoryID equals e.CategoryID
let j = new ExpenseViewModel
{
   Category = c,
   Expense = e,
 // returns null, no errors
   CatSubName = (from x in context.Categories
                               where c.CategoryID == e.CategorySubID select c.Name ).FirstOrDefault() }
group j by j.Category.Name into g
select new Group<string, ExpenseViewModel>
{
    Key = g.Key,
    Values = g.OrderBy(s => s.CatSubName)
}
).ToList();

[ExpenseViewModel]
    public class ExpenseViewModel
    {
        public Expense Expense { get; set; }
        public Category Category { get; set; }
        public Object CatSubName { get; set; }
    }
WorknHardrAsked:
Who is Participating?
 
WorknHardrConnect With a Mentor Author Commented:
Solution:

[ExpenseViewModel]
...
public string CatSubName { get; set; }
...

[LINQ]
...
let j = new ExpenseViewModel
{
  Category = c,
  Expense = e,
  CatSubName = context.Categories.Where(x => x.CategoryID == e.CategorySubID).Select(x => x.Name).FirstOrDefault() }
...
0
 
käµfm³d 👽Commented:
Your SQL isn't performing a Group By, so why are you doing so in your LINQ?
0
 
WorknHardrAuthor Commented:
SQL is just for testing the Select. I know they don't match. I do need the LINQ group etc...
0
 
WorknHardrAuthor Commented:
Figured it out myself...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.