How to use Left join linq for multiple tables

Question

I'm Stuck at the joining multiple tables.

Context: i have order table which is linked to productTable(name: ORDER_DETAILS) and recipeTable(ORDER_RECIPEs).I can order product from product menu and recipes from recipe menu. and when i click cart btn, then there must be complete list of product orders and recipe order. what am i getting, both orders are merged in single order but i want these as 2 separate rows under single orders. you can see the result in picture.

var data = (from order in orderEntities.ORDERS
                    join customer in orderEntities.CUSTOMERS on order.FK_CustomerEmail_Orders equals customer.CustomerEmail
                    join orderDetail in orderEntities.ORDER_DETAILS on order.OrderId equals orderDetail.FK_OrderId_OrderDetails into s
                    from orderDetail in s.DefaultIfEmpty()

                    join product in orderEntities.PRODUCTS on orderDetail.FK_ProductId_OrderDetails equals product.ProductId into p
                    from product in p.DefaultIfEmpty()
                    join brand in orderEntities.BRANDS on product.FK_BrandId_Products equals brand.BrandId into b
                    from brand in b.DefaultIfEmpty()


                    join orderRecipe in orderEntities.ORDER_RECIPE on order.OrderId equals orderRecipe.FK_OrderId_OrderRecipe into ro
                    from orderRecipe in ro.DefaultIfEmpty()
                    join recipe in orderEntities.RECIPEs on orderRecipe.FK_RecipeId_OrderRecipe equals recipe.RecipeId into r
                    from recipe in r.DefaultIfEmpty()
                    join rBrand in orderEntities.BRANDS on recipe.FK_BrandId_Recipes equals rBrand.BrandId into rb
                    from rBrand in rb.DefaultIfEmpty()

                        //into ps from rev in ps.DefaultIfEmpty()

                    where customer.CustomerEmail == customerEmail && order.OrderStatus == status &&
                    brandId == 960
                        //(brand.BrandId == brandId && rBrand.BrandId == brandId) 

                    orderby order.OrderId descending

Select Query

select new
                    {
                        Brand = new
                        {
                            BrandId         = brand == null ? 0 : brand.BrandId,
                            BrandName       = brand == null ? String.Empty : brand.BrandName,
                            BrandCategory   = brand == null ? String.Empty : brand.BrandCategory
                        },
                        Customer = new
                        {
                            customer.CustomerId,
                            customer.CustomerEmail,
                            customer.CustomerFirstName,
                            customer.CustomerLastName,
                            customer.CustomerMobile,
                            customer.CustomerImageUrl
                        },

                        OrderDetail = new
                        {

                            OrderDetailId           = orderDetail != null ? orderDetail.OrderDetailId : 0 ,
                            OrderDetailQuantity     = orderDetail != null ? orderDetail.OrderDetailQuantity: 0.0 ,
                            OrderDetailTime         = orderDetail != null ? orderDetail.OrderDetailPlaceTime : DateTime.Now,
                            OrderDetailProductId    = orderDetail != null ? orderDetail.FK_ProductId_OrderDetails : 0 ,
                            OrderDetailOrderId      = orderDetail != null ? orderDetail.FK_OrderId_OrderDetails : 0
                        },

                        OrderRecipe = new
                        {
                            OrderRecipeId           = orderRecipe != null ? orderRecipe.OrderRecipeId : 0,
                            orderRecipeQuantity     = orderRecipe != null ? orderRecipe.OrderRecipeQuantity : 0,
                            OrderRecipePlaceTime    = orderRecipe != null ? orderRecipe.OrderRecipePlaceTime : DateTime.Now ,
                            orderRecipeOrderId      = orderRecipe != null ? orderRecipe.FK_OrderId_OrderRecipe: 0,
                            orderRecipeRecipeId     = orderRecipe != null ? orderRecipe.FK_RecipeId_OrderRecipe :0
                        },

                        Product = new
                        {
                            ProductId               = product == null ? 0 : product.ProductId,
                            ProductTitle            = product == null ? String.Empty : product.ProductTitle,
                            ProductOldPrice         = product == null ? 0.0 : product.ProductOldPrice,
                            ProductNewPrice         = product == null ? 0.0 : product.ProductNewPrice,
                            ProductImageUrl         = product == null ? String.Empty : product.ProductImageUrl,
                            ProductContent          = product == null ? String.Empty : product.ProductContent,
                            ProductCategory         = product == null ? String.Empty : product.ProductCategory,
                            ProductSubCategory      = product == null ? String.Empty : product.ProductSubCategory,
                            ProductPostedTime       = product == null ? DateTime.Now : product.ProductPostedTime,
                            ProductStocks           = product == null ? String.Empty : product.ProductStocks,
                            ProductStatus           = product == null ? String.Empty : product.ProductStatus,
                            ProductBrandId          = product == null ? 0 : product.FK_BrandId_Products
                        },


                        Recipe = new
                        {
                            RecipeId                = recipe != null ? recipe.RecipeId: 0 ,
                            RecipeTitle             = recipe != null ? recipe.RecipeTitle : String.Empty,
                            RecipePrice             = recipe != null ? recipe.RecipePrice : 0,
                            RecipeImage             = recipe != null ? recipe.RecipeImage: String.Empty,
                            RecipeCategory          = recipe != null ? recipe.RecipeCategory: String.Empty,
                            RecipePostTime          = recipe != null ? recipe.RecipePostTime : DateTime.Now,
                            RecipeStock             = recipe != null ? recipe.RecipeStock: String.Empty,
                            RecipeStatus            = recipe != null ? recipe.RecipeStatus : false,
                            ProductBrandId          = recipe != null ? recipe.FK_BrandId_Recipes: 0 
                        },


                        order.OrderId,
                        order.OrderPlaceTime,
                        order.OrderCompletedTime,
                        order.OrderStatus,
                        order.FK_CustomerEmail_Orders

                    }).Skip(offset).Take(limit).ToList();

I have followed this : Left Join Linq

you can see here, Products and recipe are combined in same order but if product is there, recipe should be 0 and vice versa. like this:

order:{
    brand:{ 10 },OrderRecipe:{ 1 },Recipe{1}, orderDetail:{ 0 },products: {0} orderId: 1 ..},{
    brand:{ 10 },OrderRecipe:{ 2 },Recipe{2}, orderDetail:{ 0 },products: {0} orderId: 1 ..},{  
    brand:{ 10 },orderDetail:{ 1 },products: {1},OrderRecipe:{ 0},Recipe{0} orderId: 1...},{
    brand:{ 10 },orderDetail:{ 2 },products: {2},OrderRecipe:{ 0},Recipe{0} orderId: 1...}

If there's any other better way to do this. kindly correct me here.

i have uploaded the picture.. you can see orderRecipe and OrderDetail both are showing over there. how can i manage it like if one is null, won't show... — , Mar 12 '19 at 06:19

score 0 · Answer 1 · answered Mar 18 '19 at 08:14

You will surely get the result like that, because you have joined both tables with your ORDER.

What you can do is: 1) you can make the objects separately like this:

var recipe = (from db.order ...
join orderRecipe in orderEntities.ORDER_RECIPE on order.OrderId equals orderRecipe.FK_OrderId_OrderRecipe into ro
                    from orderRecipe in ro.DefaultIfEmpty()
                    join recipe in orderEntities.RECIPEs on orderRecipe.FK_RecipeId_OrderRecipe equals recipe.RecipeId into r
                    from recipe in r.DefaultIfEmpty()
                    join rBrand in orderEntities.BRANDS on recipe.FK_BrandId_Recipes equals rBrand.BrandId into rb
                    from rBrand in rb.DefaultIfEmpty())

and after that you can use both the objects accordingly

2) if you want to use join on both in same query. check your generated sql against linq in visual studio. what you gonna do is use right join for your products and left for your recipes..

i hope this will help you.

something like this...! `if (product.Count() != 0 || recipe.Count() !=0)` `return Ok(new ` `Product=product, Recipe=recipe` — , Mar 18 '19 at 12:03

How to use Left join linq for multiple tables

1 Answers1