Custom linq query for entity framework & ASP.NET MVC app

See more:

LINQ

jQuery

MVC5

entity-framework

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 tables as below

• Assets
• Category
• Term

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

What I have tried:

I have tried stored procedure but get null values in table

Queries by me:

<pre> public ActionResult GetData1()
            {
                //Display for Assets Page
                using (MetadataEntities db = new MetadataEntities())
                {
                    List<Asset> c = db.Assets.ToList();
                    List<Category> ct = db.Categories.ToList();
                    List<Term> t = db.Terms.ToList();

  var subQuery1 = from ch in c
                  join pa in c on ch.ASSET_INT_ID equals pa.PARENT_ASSET_ID into tab1
                  from pa in tab1.DefaultIfEmpty()
    
                  select new Asset
                {
                   DISPLAY =ch.DISPLAY,
                   TYPE = ch.TYPE,
                   parentasset = pa.DISPLAY,
                   CATEGORY_INT_ID = ch.CATEGORY_INT_ID
                  };
    
    
var subQuery2 = from CH in ct
              join PA in ct on CH.CATEGORY_INT_ID equals PA.PARENT_CATEGORY_ID into tab2
              from PA in tab2.DefaultIfEmpty()
              select new Category
            {
               DISPLAY = CH == null ? string.Empty : CH.DISPLAY,
               ParentCategory = PA == null ? string.Empty : PA.DISPLAY,
               CATEGORY_INT_ID = CH.CATEGORY_INT_ID
             };
    
var mainQuery = from tab1 in subQuery1
                join Cat in subQuery2 on tab1.CATEGORY_INT_ID equals Cat.CATEGORY_INT_ID                                                                                         
                join term in t on Cat.CATEGORY_INT_ID equals term.CATEGORY_INT_ID 
                                    
                select new Asset
           {
                 DISPLAY = tab1 == null ? string.Empty : tab1.DISPLAY,
                 TYPE = tab1 == null ? string.Empty : tab1.TYPE,
                 parentasset = tab1 == null ? string.Empty : tab1.parentasset,
                 assetcategory = Cat == null ? string.Empty : Cat.DISPLAY,
                 parentcategory = Cat == null ? string.Empty : Cat.ParentCategory,
                 bterm = term == null ? string.Empty : term.BUSINESS_TERM,
                SHORT_DESCRIPTION = term == null ? string.Empty : term.SHORT_DESCRIPTION
            };
    
    
 
                    return Json(new { data = mainQuery }, JsonRequestBehavior.AllowGet);
                }
    
    
            }

It returns 44,557 entries instead of 56932