none
QUERYING , GROUPING RESULTS, AND SELECTING DATA USING ENTITY FRAMEWORK V.5 RRS feed

  • Question

  • Hi all! I need an assistance from anyone with an idea how can i perform the grouping of my data results (View image shown below) which i need to be returned from database by using ENTITY FRAMEWORK Version 5. I'm using C# in my code programming. I tried a little bit by writing the following [SQL Query] in Microsoft SQL Server Management studio 2018, this works good BUT i want to implement this Querying and grouping results  by USING ENTITY FRAMEWORK to obtain the same output as this one shown on the this image  below:

    OUTPUT RESUTS

    What I have tried:

    /** This is an SQL QUERY to query data from database table and

    then group by some conditions as shown on query below ***/

    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])

    And  my database table have these columns:

    Database Table

             ---------------------------  Thanks  --------------------------


    #VB.Net




    Friday, October 4, 2019 8:02 AM

All replies

  • You do know that EF can run a Stored Procedure with the T-SQL you are showing,  and you can use the results of the query.

    The results of the query can be populated to a custom object such as a DTO in a List<T> or List<of T> in VB.

    You can do this in EF  and use the EF backdoor.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    https://blogs.msdn.microsoft.com/alexj/2009/03/25/tips-and-tricks/

    https://www.codeguru.com/vb/gen/vb_misc/oop/article.php/c7063/Data-Transfer-Object-Pattern-Goes-VBNET.htm

    https://www.codeproject.com/articles/1050468/data-transfer-object-design-pattern-in-csharp

    You can use auto properties.

    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/auto-implemented-properties

    The example shows how to address the resultset that will be retuned by the stored procedure, showing you how can read the resultsest row by column name instead of ordinal index position  

    https://www.dotnetheaven.com/article/read-the-data-with-the-datareader-object-in-vb.net

    Friday, October 4, 2019 11:42 AM
  • Hi!

    Thanks for your guidance but please understand that  i want to use CODE FIRST APPROACH !!!


    I tried but it looks like The Entity framework Doesn't like the syntax as we use in T-SQL !

    I converted the T-SQL (in question above) into stored Procedures and i named it  "GetAllContracts"

    Below  is a stored procedures  "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

    Then i execute the stored procedure above passing it to the Context.Database.Sqlquery () method of Contex as follows:

    I Created a Sub LoadGrid () which I call it on form load event

    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;
         }

    But It throws an Exception shown on image below:

    Error Message

    This is a Context and Modal Class  i created because all i'm using is Code First Approach!!

     

     [Table("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 still can’t understand What’s wrong with my code and how to fix it.

    Any help please ,,,How to fix that exception make it work.

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


    #VB.Net


    Saturday, October 5, 2019 10:03 AM
  • EF is not a panacea solution, and sometimes, you have to use the EF backdoor using straight up ADO.NET, SQL Command objects and execute dynamic T-SQL or a sproc using the existing EF connection, but EF is not involved, which the link I gave you explains. 

    • Edited by DA924x Saturday, October 5, 2019 2:08 PM
    Saturday, October 5, 2019 2:07 PM
  •  

    I did this it worked using your technique of Entity Framework  Backdoor as explained here:How to execute T-SQL directly against the database. But i wasn't  happy why the Entity Frameworks failing to run my Queries until i turn back to ADO.NET and requiring me write a lot of code just to query and group my data results.!!!!!

    Here is the code that worked for querying my data and grouping as per my requirements 

    private void LoadGrid()
              {        
                using (var db = new MyFirstDBContext()) 
                {
                    SqlConnection sqlconn = new SqlConnection(db.Database.Connection.ConnectionString);
                    if (sqlconn.State == ConnectionState.Closed)
                    {
                        sqlconn.Open();
                    }
                    SqlCommand cmd = new SqlCommand("GetAllContracts", sqlconn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                    DataTable dt = new DataTable();
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                    dgvData.DataSource = dt;
                    sqlconn.Close();
               
             }
    

    When Form1 loads i call the sub LoadGrid ( ) to show data 

    private void Form1_Load(object sender, EventArgs e)
            { 
                LoadGrid();
                lblID.Text = "0";
            }

     Please suggest If there is another approach to use rather than this..

     Thanks.


     


    #VB.Net

    Saturday, October 5, 2019 7:29 PM
  • 1) Using a datatable is not optimal programming,  and you should be using a List<T> of custom objects or creating a single object using  an ADO.NET datareader looping on the datareader reading the results from the sproc, making a custom object like a DTO, populating the DTO from the results of the sproc read by the datareader and loading the custom object into the List<T>.

    https://dzone.com/articles/reasons-move-datatables

    https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    The link shows you how to use a datareader.

    https://www.akadia.com/services/dotnet_data_reader.html

    Again, use the EF backdoor using the existing EF connection to the database.

    var dtos = new List(DTO);

    using (var ctx = new EFDBContext())
    {
    var entityConn = ctx.Connection as EntityConnection; var dbConn = entityConn.StoreConnection as SqlConnection; dbConn.Open(); var cmd = new SqlCommand("GetAllContracts", dbConn); cmd.CommandType = CommandType.StoredProcedure; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var dto = new DTO()

    dto.Name = reader.["Name"].ToString(); dto.Address = reader.["Address"].ToString(); dtos.Add(dto); ); }

    }


    You return the List<DTO> and bind it to the control.


    • Edited by DA924x Sunday, October 6, 2019 5:22 AM
    Saturday, October 5, 2019 11:30 PM