How to do querying , grouping of results, and selecting data using entity framework code first approach

Question

How can i use Entity Framework Code First Approach to perform querying and grouping of my data results from SQL Server to get results shown on this image:

IMAGE RESULTS

I have tried to write some code as shown below but Entity Framework doesn't like and it throws some exceptions :
ERROR SCREEN

complaining that data reader can't find some columns to match columns i have specified in the following Entity class which i have created in this Class.

I was thinking may be the T-SQL Query below are not supported in Entity Framework!

SELECT CASE WHEN GROUPING && GROUP BY

.....................................................................................................

Below is Class ContractBOQ and Context class MyFirstDBContext

Table("ContractBOQ")]           // map to an existing Table called ContractBOQ
public class ContractBOQ
{
    [Key]
    [Column("SN")]
    public int SN { get; set; }
    [Required]
    [Column("Item Code")]
    public string Item_Code { get; set; }
    [Required]
    [Column("Description")]
    public String Description { get; set; }
    [Required]
    [Column("Unit")]
    public string Unit { get; set; }
    [Required]
    [Column("Quantity")]
    public decimal Quantity { get; set; }
    [Required]
    [Column("Approaved Rate")]
    public decimal Approaved_Rate { get; set; }
    [Required]
    [Column("Contract Rate")]
    public decimal Contract_Rate { get; set; }
    [Required]
    [Column("Approved Amount")]
    public decimal Approved_Amount { get; set; }
    [Required]
    [Column("Contract Amount")]
    public decimal Contract_Amount { get; set; }
    [Required]
    [Column("BOQ Part")]
    public string BOQ_Part { get; set; }

    [Required]
    [Column("Contract ID")]
    public string Contract_ID { get; set; }

    [Required]
    [Column("Road Name")]
    public string Road_Name { get; set; }

    [Required]

    [Column("Road Code")]
    public string Road_Code { get; set; }
}

public class MyFirstDBContext : DbContext
{
    public virtual DbSet<ContractBOQ> ContractBOQ { get; set; }

}

I converted the T-SQL below into stored Procedures and i named it GetAllContracts so that i can execute it as Query by passing it to the Context.Database.Sqlquery ("GetAllContracts") method of Context class.

Below is a stored procedure GetAllContracts i created:

CREATE OR ALTER PROC [dbo].[GetAllContracts] AS BEGIN

Select case  when grouping([SN])=1 THEN 'Sub-Total:' 
else CAST( SN as nvarchar) end SN,
case  when grouping ([Item Code])=1 THEN [Road Name] 
else [Item Code] end as [Item Code],
case  when grouping ([Description])=1 THEN [BOQ Part] 
else  [Description] end as [Description],
case  when grouping ([Unit])=1 THEN '*  *  *' ELSE [Unit]
end as [Unit],
[Quantity],[Approaved Rate] ,[Contract Rate],
sum([Approved Amount]) as[Approved Amount]
From [MyFirstDB].[dbo].[ContractBOQ]  
where [Contract ID]='RH/NTC/2019-2020/Q/01'
group by grouping sets
((SN,[Item Code],[Description],[Unit] ,[Quantity] ,
 [Approaved Rate] ,[Contract Rate],[Approved Amount],
[Contract Amount], 
[BOQ Part],[Contract ID],[Road Name],[Road Code]),
([Road Name],[BOQ Part]),([Road Name]),()) ORDER BY 
GROUPING([Road Name]) 

END

I then Created a Sub named LoadGrid ( ) which I call it during Form1_Load ( ) event of the form 'Form1'

   private void LoadGrid()
    {
        Application.DoEvents();
        Cursor.Current = Cursors.WaitCursor;

        using (var db = new MyFirstDBContext())
        {
        var data = db.ContractBOQ.SqlQuery("GetAllContracts");
        dgvData.DataSource = data.ToList();

        dgvData.Refresh();
        dgvData.ReadOnly = true;
        }

       Cursor.Current = Cursors.Default;
    }

These are columns to the existing Table Named ContractBOQ in Database:
Table-ContractBOQ

When i run i get this error:
ERROR SCREEN

Any help to fix this error and make it work..

I thought a person used LINQ (Language INtegraged Queries) for interacting in Entity-driven projects? https://learn.microsoft.com/en-us/dotnet/csharp/linq/index — J. Murray, Oct 05 '19 at 12:20
If you are going to end up putting all business logic into stored procedures (not necessarily a bad thing), why use an ORM? It defeats the purpose of an ORM and also defeats the performance benefits of stored procedures with all the conversion to object graphs typically done by an ORM. Just use straight-up ADO.NET — , Oct 05 '19 at 12:37
Also, never never never never never never never call `Application.DoEvents()` - it can lead to your UI becoming **re-entrant**! It's a particularly bad method with its history from [VB6 days](https://stackoverflow.com/a/4526667/585968) — , Oct 05 '19 at 12:39
@MickyD if so, what's the solution i should opt for ? but i understand i can still use stored procedures inside ORM — mk2tk2, Oct 05 '19 at 12:52
@MickyD You know why I write stored procedures is because i think entity frameworks doesn't allow complex query for data manipulations as we do in SQL queries!!. This is because the same stored procedures when executed inside Microsoft SQL Server seems to work BUT the same stored procedures when trying to execute inside Entity framework doesn't work. Is this right ??? Correct me if i'm wrong because we all learn from others and from mistakes we do. — mk2tk2, Oct 05 '19 at 13:04
Certainly, SPs generally offer better performance as you have more control of the queries itself and can benefit from query plans. However in your case the problem seems to be that your ORM model doesn't match the data coming back from SQL. You need to ensure your representing the same columns and types. — , Oct 05 '19 at 23:27

How to do querying , grouping of results, and selecting data using entity framework code first approach

0 Answers0