How can I modify this linq query to return some empty fields instead of just omitting the data that doesn't fit the join?

Question

public IQueryable<RecentlyCreatedAssetViewModel> getRecentlyCreatedAssetsByCompanyID(int companyID)
        {
            return (from a in db.Assets 
                    join ab in db.AssetBundles on a.AssetID equals ab.AssetID 
                    join b in db.Bundles on ab.BundleID equals b.BundleID
                    where a.CompanyID == companyID && a.AssetTypeID == 11 && a.IsActive == true && a.ShowInResults == true
                    orderby a.CreateDate descending 
                    select new RecentlyCreatedAssetViewModel { AssetID = a.AssetID, AssetName = a.AssetName, AssetTypeID = a.AssetTypeID, BundleIcon = b.BundleIcon, BundleName = b.BundleName }).Take(10);
        }

It turns out that I want to also get back some db.Assets that do not have relations in db.AssetBundles, however I am not sure how to do this, I want to put empty space (blank strings) in the place of the Bundle fields of the RecentlyCreatedAssetViewModel when there is no relation. This query will not return an Asset that has no relation in the joins though, how can I change this so that it will give them back just putting empty strings in the missing data?

Answer 1

Use a LEFT JOIN in your query to get the job done.

More information can be found at W3Schools.

Answer 2

Here's an article about doing LEFT JOINs in Linq-to-sql; Essentially what you are looking for is the DefaultIfEmpty() extension.

Answer 3

What you need is to perform a left outer join, check out the reference on the join clause in MSDN, and scroll down to the section titled 'Left Outer Join'