How to perform sql select query with additional fields in EF Core?

Question

I have the next model:

[Table("clients")]
    public class ClientDto : BaseModelDto
    {
        [Required]
        [StringLength(70, MinimumLength = 3)]
        [Column("fio")]
        public string FIO { get; set; }

        [StringLength(100, MinimumLength = 3)]
        [Column("address")]
        public string Address { get; set; }

        [Phone]
        [Column("phone")]
        public string Phone { get; set; }        

        public List<RealizationDto> Realizations { get; set; }
    }

And I need to perform sql query and get the result:

public async Task<ClientDto> GetTheMostValuableCustomer()
    {
      var result = await Context.Clients.FromSql<ClientDto>(@"
        select clients.id, clients.fio, clients.address, clients.phone, sum(goods.price*realizations.quantity) max_cost
            from clients
            inner join realizations on clients.id=realizations.client_id
            inner join goods on realizations.good_id=goods.id
            group by clients.id
            order by max_cost desc
        limit 1;
    ").ToListAsync();

  return result.FirstOrDefault();
}

This quesry works in PostgreSql, but doesn't work on server level, because ClientDto model and select fields are different. (max_cost doesn't exist in ClientDto model). But when I execute query I get an error that the field "max_cost" doesn't exist. How can I resolve this problem?

What if you move the `max_cost` to the order by instead of the select list?. e.g. `order by sum(goods.price*realizations.quantity)` — D Stanley, Dec 13 '17 at 22:52

score 0 · Answer 1 · answered Dec 13 '17 at 22:56

0

You need to change:

sum(goods.price*realizations.quantity) max_cost

to:

sum(goods.price*realizations.quantity) AS max_cost

answered Dec 13 '17 at 22:56

SoronelHaetir

14,104
1
12
23

I think these constructions are equaled – user190794 Dec 14 '17 at 10:30

How to perform sql select query with additional fields in EF Core?

1 Answers1