Write a Custom SQL Query Using Entity Framework

In order to write custom SQL queries first, we need to create a class to hold the results.

 public class CustomerBalance
        public int CustomerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        [Display(Name = "Mex Balance")]
        public decimal TotalCost { get; set; }

        [Display(Name = "USA Balance")]
        public decimal TotalUsa { get; set; }
        [Display(Name = "Customer Name")]
        public string CustomerName
                return FirstName + " " + LastName;

Then we need to add it to the DbContext, since we are not using a table with a primary key, we also have to add a HasNoKey property.

  public DbSet<customerbalance> CustomerBalance { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
                .Entity<customerbalance>(eb =&gt;

And finally, we can add our query:

 public async Task<List<CustomerBalance>> GetCustomerBalanceAsync()

            var query = @"SELECT    c.Id as CustomerId,
                                    SUM(s.Cost) as TotalCost, 
                                    SUM(s.AmountPaid - s.Cost) as TotalUsa
                        FROM Service s 
		                        JOIN Account a on s.AccountId = a.Id
		                        JOIN Customer c on a.CustomerId=c.Id
                        WHERE s.Paid = 1
                        Group By c.Id, c.FirstName, c.LastName";
           var command = await  _context.CustomerBalance.FromSqlRaw(query).ToListAsync();

            return command;


Related Posts

Comments are closed.