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