How to get name of product from many to many even if it is not in the related table SQL or EF linq q-CodePudding

I want to get all product names with category if even product doesn't have a category

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<CategoryProduct> CategoryProducts { get; set; }
}

public class CategoryProduct
{
    public int CategoryProductId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

internal class EFDbContext : DbContext, IDBProductContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<CategoryProduct> CategoryProducts { get; set ; }

    public EFDbContext()
    {
        Database.SetInitializer<EFDbContext>(new DropCreateDatabaseIfModelChanges<EFDbContext>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().HasMany(p => p.CategoryProducts)
            .WithMany(c => c.Products)
            .Map(pc => {
                pc.MapLeftKey("ProductRefId");
                pc.MapRightKey("CategoryProductRefId");
                pc.ToTable("CategoryProductTable");
            });
        base.OnModelCreating(modelBuilder);
    }
}

If I write a SQL query like this, I get all of them from joined EF table

SELECT p.Name, cp.Name 
FROM CategoryProductTable AS cpt, 
     CategoryProducts AS cp, Products as p
WHERE 
    p.ProductId = cpt.ProductRefId 
    AND cp.CategoryProductId = cpt.CategoryProductRefId

but I want to get all from product names with category if even product doesn't have a category

UPDATED: thanks for SQL solution @Nick Scotney, but now I would want know how it do it in Linq

CodePudding user response：

Could you be after a "LEFT OUTER JOIN" in your Sql?

SELECT
    p.Name,
    cp.Name
FROM
    Products p
        LEFT OUTER JOIN CategoryProductTable cpt ON p.ProductId = cpt.ProductRefId
        LEFT OUTER JOIN CategoryProducts cp ON cpt.CategoryProductRefId = cp.CategoryProductId

In the above SQL, everything from products will be selected, regardless of if there is a Category or not. When there isn't a category, cp.Name will simply return NULL.

CodePudding user response：

You would want to remove p.ProductId = cpt.ProductRefId predicate if you want to show all products.

SELECT p.Name, cp.Name 
FROM CategoryProductTable as cpt, CategoryProducts as cp, Products as p
WHERE cp.CategoryProductId = cpt.CategoryProductRefId

This will assign each category to every product.