Introduction

While working with Entity Framework LINQ people ususally get struck on how to use joins, group by cluase, count etc..

Joins take two source sequences as input. The join clause compares the specified keys for equality by using equals keyword. Their are different types of joins; depending upon our senario, we can use any one.

Example

Suppose, there is a shop which contains multiple products. The shop owner can add a discount for each product or category.

I am going to create two tables, first for DISCOUNT includes Product Category Name, Discount in percentage; and second one is PRODUCT that includes Product Names, Amount, Discount id (which is Foreign key from discount table).

We will use different types of joins, and group by clause, to show the actual number of count of that particular category type. Below is the table script which has some dummy data.
  1. GO
  2. SET ANSI_NULLS ON
  3. GO
  4. SET QUOTED_IDENTIFIER ON
  5. GO
  6. SET ANSI_PADDING ON
  7. GO
  8. CREATE TABLE [dbo].[DISCOUNT](
  9. [DI_ID] [int] IDENTITY(1,1) NOT NULL,
  10. [DI_NAME] [varchar](50) NULL,
  11. [DI_PER] [decimal](18, 2) NULL,
  12. CONSTRAINT [PK_SALARY_HIKE] PRIMARY KEY CLUSTERED
  13. (
  14. [DI_ID] ASC
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  16. ) ON [PRIMARY]
  18. GO
  19. SET ANSI_PADDING OFF
  20. GO
  21. SET ANSI_NULLS ON
  22. GO
  23. SET QUOTED_IDENTIFIER ON
  24. GO
  25. SET ANSI_PADDING ON
  26. GO
  27. CREATE TABLE [dbo].[PRODUCT](
  28. [PR_ID] [int] IDENTITY(1,1) NOT NULL,
  29. [PR_NAME] [varchar](50) NULL,
  30. [PR_AMOUNT] [decimal](18, 2) NULL,
  31. [PR_DI_ID] [int] NULL,
  32. CONSTRAINT [PK_PRODUCT_1] PRIMARY KEY CLUSTERED
  33. (
  34. [PR_ID] ASC
  35. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  36. ) ON [PRIMARY]
  38. GO
  39. SET ANSI_PADDING OFF
  40. GO
  41. SET IDENTITY_INSERT [dbo].[DISCOUNT] ON
  43. GO
  44. INSERT [dbo].[DISCOUNT] ([DI_ID], [DI_NAME], [DI_PER]) VALUES (1, N'ELECTRONICS', CAST(10.00 AS Decimal(18, 2)))
  45. GO
  46. INSERT [dbo].[DISCOUNT] ([DI_ID], [DI_NAME], [DI_PER]) VALUES (2, N'HOME APPLIANCES', CAST(5.50 AS Decimal(18, 2)))
  47. GO
  48. INSERT [dbo].[DISCOUNT] ([DI_ID], [DI_NAME], [DI_PER]) VALUES (3, N'CAR & ACCESSORIES', CAST(12.00 AS Decimal(18, 2)))
  49. GO
  50. INSERT [dbo].[DISCOUNT] ([DI_ID], [DI_NAME], [DI_PER]) VALUES (4, N'CLOTHS', CAST(25.25 AS Decimal(18, 2)))
  51. GO
  52. SET IDENTITY_INSERT [dbo].[DISCOUNT] OFF
  53. GO
  54. SET IDENTITY_INSERT [dbo].[PRODUCT] ON
  56. GO
  57. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (1, N'Mobile 1', CAST(35000.00 AS Decimal(18, 2)), 1)
  58. GO
  59. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (2, N'Shirt', CAST(1200.00 AS Decimal(18, 2)), 4)
  60. GO
  61. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (3, N'Wood Table', CAST(3500.00 AS Decimal(18, 2)), 2)
  62. GO
  63. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (4, N'BMW X3', CAST(4340000.00 AS Decimal(18, 2)), 3)
  64. GO
  65. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (5, N'Laptop', CAST(75000.00 AS Decimal(18, 2)), NULL)
  66. GO
  67. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (6, N'Shampoo ', CAST(150.00 AS Decimal(18, 2)), NULL)
  68. GO
  69. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (7, N'Ice Cream', CAST(100.00 AS Decimal(18, 2)), NULL)
  70. GO
  71. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (8, N'Mobile 2', CAST(27000.00 AS Decimal(18, 2)), 1)
  72. GO
  73. INSERT [dbo].[PRODUCT] ([PR_ID], [PR_NAME], [PR_AMOUNT], [PR_DI_ID]) VALUES (9, N'Mobile 3', CAST(22000.00 AS Decimal(18, 2)), 1)
  74. GO
  75. SET IDENTITY_INSERT [dbo].[PRODUCT] OFF
  76. GO
Inner Join

The exmaple shows a simple Innner join query. This query shows the list of product names, amount, discount, net amount - those who having matching elements with discount pairs. If product does not have any discount id, then this will not display.

First, create .edmx file from the database. Right click on Project solution => Add => New Item, then from Data, select ADO.NET Entity Data Model & give any name ( example- sample).

Now, give connection string, select Database Name, your two tables & after successfully adding, just build your solution. Now, in our class, create object of DB Enitity so it will be able to access our tables from DBContext as
  1. Temp_DBEntities objDBEntity = new Temp_DBEntities();
In this example, I am going to create a datatable so we can see the actual result. Add columns to the datatables. In our join, I am going to do some calculations to apply appropriate discount on products, as shown below.
  1. DataTable dt = new DataTable();
  2. dt.Columns.Add("Product Name", typeof(string));
  3. dt.Columns.Add("Price", typeof(decimal));
  4. dt.Columns.Add("Discount %", typeof(decimal));
  5. dt.Columns.Add("Discount Amount", typeof(decimal));
  6. dt.Columns.Add("Net Amount", typeof(decimal));
  7. var joinResult = (from p in objDBEntity.PRODUCTs
  8. from d in objDBEntity.DISCOUNTs
  9. where p.PR_DI_ID == d.DI_ID
  10. select new
  11. {
  12. PNAME = p.PR_NAME,
  13. PAMT = p.PR_AMOUNT,
  14. PDISPER = d.DI_PER == null ? 0 : d.DI_PER,
  15. PDISAMT = d.DI_PER == null ? 0 : (p.PR_AMOUNT * d.DI_PER / 100),
  16. PNETAMT = (p.PR_AMOUNT - (d.DI_PER == null ? p.PR_AMOUNT : (p.PR_AMOUNT * d.DI_PER / 100))),
  17. }).ToList();
  18. foreach (var item in joinResult)
  19. {
  20. DataRow row = dt.NewRow();
  21. string name = Convert.ToString((item.PNAME).ToString());
  22. decimal price = Convert.ToDecimal((item.PAMT).ToString());
  23. decimal per = Math.Round(Convert.ToDecimal(item.PDISPER), 2, MidpointRounding.AwayFromZero);
  24. decimal dAmt = Math.Round(Convert.ToDecimal(item.PDISAMT), 2, MidpointRounding.AwayFromZero);
  25. decimal netAmt = Math.Round(Convert.ToDecimal(item.PNETAMT), 2, MidpointRounding.AwayFromZero);
  26. row["Product Name"] = name;
  27. row["Price"] = price;
  28. row["Discount %"] = per;
  29. row["Discount Amount"] = dAmt;
  30. row["Net Amount"] = netAmt;
  31. dt.Rows.Add(row);
  32. }
If we put debugger & check datatable dt into watch, then our output will be the following.



Outer Join

Following exmaple shows a simple Outer Left join query. All the elements in the left source sequence will be displayed, even if there is no matching element with right source.
  1. DataTable dt1 = new DataTable();
  2. dt1.Columns.Add("Product Name", typeof(string));
  3. dt1.Columns.Add("Price", typeof(decimal));
  4. dt1.Columns.Add("Discount %", typeof(decimal));
  5. dt1.Columns.Add("Discount Amount", typeof(decimal));
  6. dt1.Columns.Add("Net Amount", typeof(decimal));
  8. var leftJointResult = (from p in objDBEntity.PRODUCTs
  9. join d in objDBEntity.DISCOUNTs on new { DID = (int?)p.PR_DI_ID } equals new { DID = (int?)d.DI_ID } into dis
  10. from d in dis.DefaultIfEmpty()
  11. select new
  12. {
  13. PNAME = p.PR_NAME,
  14. PAMT = p.PR_AMOUNT,
  15. PDISPER = d.DI_PER == null ? 0 : d.DI_PER,
  16. PDISAMT = d.DI_PER == null ? 0 : (p.PR_AMOUNT * d.DI_PER / 100),
  17. PNETAMT = p.PR_DI_ID == null ? p.PR_AMOUNT : (p.PR_AMOUNT - (p.PR_AMOUNT * d.DI_PER / 100)),
  18. }).ToList();
  20. foreach (var item in leftJointResult)
  21. {
  22. DataRow row = dt1.NewRow();
  23. string name = Convert.ToString((item.PNAME).ToString());
  24. decimal price = Convert.ToDecimal((item.PAMT).ToString());
  25. decimal per = Math.Round(Convert.ToDecimal(item.PDISPER), 2, MidpointRounding.AwayFromZero);
  26. decimal dAmt = Math.Round(Convert.ToDecimal(item.PDISAMT), 2, MidpointRounding.AwayFromZero);
  27. decimal netAmt = Math.Round(Convert.ToDecimal(item.PNETAMT), 2, MidpointRounding.AwayFromZero);
  28. row["Product Name"] = name;
  29. row["Price"] = price;
  30. row["Discount %"] = per;
  31. row["Discount Amount"] = dAmt;
  32. row["Net Amount"] = netAmt;
  33. dt1.Rows.Add(row);
  34. }
If we put debugger & check datatable dt1 into watch, then our output will be the following.

Group By, Count

I am going to create one model here as so this will be used to get records when we execute group by query clause to get count of product on categoery.
  1. public class ProductInfo
  2. {
  3. public string PNAME { get; set; }
  4. public decimal? PAMT { get; set; }
  5. public decimal? PDISPER { get; set; }
  6. public decimal? PDISAMT { get; set; }
  7. public decimal? PNETAMT { get; set; }
  8. public int? PCOUNT { get; set; }
  9. public int? PRDID { get; set; }
  10. }
In Below code, first we are selecting record from table into a variable by using our model & then we apply group by clause & count so we will get count.
  1. DataTable dt2 = new DataTable();
  2. var groupByResult = (from p in objDBEntity.PRODUCTs
  3. from d in objDBEntity.DISCOUNTs
  4. where p.PR_DI_ID == d.DI_ID
  5. select new ProductInfo
  6. {
  7. PNAME = p.PR_NAME,
  8. PAMT = p.PR_AMOUNT,
  9. PDISPER = d.DI_PER == null ? 0 : d.DI_PER,
  10. PDISAMT = d.DI_PER == null ? 0 : (p.PR_AMOUNT * d.DI_PER / 100),
  11. PNETAMT = (p.PR_AMOUNT - (d.DI_PER == null ? p.PR_AMOUNT : (p.PR_AMOUNT * d.DI_PER / 100))),
  12. PCOUNT = (int?)0,
  13. PRDID = p.PR_DI_ID
  14. }).ToList();
  16. foreach (var item in groupByResult)
  17. {
  18. var rCount = (from p in objDBEntity.PRODUCTs
  19. where p.PR_DI_ID == item.PRDID
  20. group p by
  21. new
  22. {
  23. d_Id = p.PR_DI_ID
  24. } into s
  25. select new
  26. {
  27. dIDs = s.Key.d_Id,
  28. cnt = s.Count()
  29. }).FirstOrDefault();
  30. item.PCOUNT = rCount == null ? 0 : (int?)rCount.cnt;
  31. DataRow row = dt2.NewRow();
  32. row["Product Name"] = item.PNAME;
  33. row["Price"] = item.PAMT;
  34. row["Discount %"] = item.PDISPER;
  35. row["Discount Amount"] = item.PDISAMT;
  36. row["Net Amount"] = item.PNETAMT;
  37. row["Net Amount"] = item.PNETAMT;
  38. row["Product Count"] = item.PCOUNT;
  39. dt2.Rows.Add(row);
  40. }
If we put debugger & check datatable dt2 into watch then our output will be,


Summary

In this article, you learned How to use Joins, Group By Clause in Entity Framework with Linq C#.