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; }
        [NotMapped]
        [Display(Name = "Customer Name")]
        public string CustomerName
        {
            get
            {
                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)
        {
            modelBuilder
                .Entity<customerbalance>(eb =&gt;
                {
                    eb.HasNoKey();
                   
                });
        }

And finally, we can add our query:

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

            var query = @"SELECT    c.Id as CustomerId,
                                    c.FirstName, 
                                    c.LastName, 
                                    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;

        }