Custom Linq query for Entity Framework & ASP.NET MVC app

I have been working on an ASP.NET MVC app using Entity Framework. Also, it's my first time developing an ASP.NET MVC app. I have been struggling (close to a month of trying and googling) to write a linq query to display results in jQuery datatable for the below SQL query. It involves various left joins and some columns have null values. It would be great if someone could help me out on this. There are 3 databases as below

Assets
Category
Term

SQL QUERY

SELECT Asset.Name AS Name, Asset.Type AS Type, Asset.Parent_Asset AS "Parent Asset", Cat.Category AS Category, Cat.Parent_Category AS "Parent Category",
T.BUSINESS_TERM AS "Business Term",
T.SHORT_DESCRIPTION AS Description FROM (SELECT CH.DISPLAY AS Name, CH.TYPE AS Type, PA.DISPLAY AS Parent_Asset, CH.CATEGORY_INT_ID
FROM [Metadata].[dbo].[Asset] CH LEFT JOIN [Metadata].[dbo].[Asset] PA ON PA.PARENT_ASSET_ID = CH.ASSET_INT_ID) Asset
LEFT JOIN (SELECT CH.DISPLAY AS Category, PA.DISPLAY AS Parent_Category, CH.CATEGORY_INT_ID AS Category_Id
FROM [METADATA].[dbo].[Category] CH LEFT JOIN [METADATA].[dbo].[Category] PA ON PA.PARENT_CATEGORY_ID = CH.CATEGORY_INT_ID) Cat ON Asset.CATEGORY_INT_ID = Cat.Category_Id
LEFT JOIN [Metadata].[dbo].[Term] T ON T.CATEGORY_INT_ID = Cat.Category_Id

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Bohdan Stupak6y ago
The closest thing to left join in ef core is Inlcude method.
In order to get a detailed explanation, you should refer to this article.
https://docs.microsoft.com/en-us/ef/core/querying/related-data
And also this post on loading nested entities
https://stackoverflow.com/questions/52711000/entity-framework-core-recursive-parent-child-linking
Roughly your will look something like this
_context.Assets
    .Include(a => a.Parent)
    .Include(a => a.Category)
    .ThenInclude(c => c.Parent)
    .Include(a => a.Term)
    .Select(a => new {a.Name, a.Parent, a.Category.Category, a.Category.Parent, a.Term.BusinessTerm, a.Term.ShortDescription})