0

I'm figuring out how to use LINQ and I'm beginning to understand more and more about it. However I have one SQL query that I can't figure out how to convert to LINQ.

I have a database table that contains information about car rentals. From this table i need to query the top 10 containing the owners who rented the most cars, the id of the owner and the name of the owner. The SQL query looks like this:

SELECT TOP 10 owner_name, MAX(calculated_owner_rental_count) as totalRentals, owner_id
FROM[rentals]
WHERE owner_name IS NOT NULL
GROUP BY owner_name, owner_id
ORDER BY totalRentals DESC

Now I tried to convert this to LINQ and automatically fill an object and add it to a list. My try so far:

private List<TopOwner> GetTopOwners() {
  return Rentals
     .Select(x => new TopOwner { OwnerName = x.OwnerName, CalculatedOwnerRentalCount = Rentals.Max(x => x.CalculatedOwnerRentalCount), OwnerId = x.OwnerId })
    .Where(x => x.OwnerName != null)
    .OrderByDescending(x => x.CalculatedOwnerRentalCount)
    .GroupBy(x => new { x.OwnerName, x.OwnerId })
    .Take(10)
    .ToList();
}

However on below the line CalculatedOwnerRentalCount = Rentals.Max(x => x.CalculatedOwnerRentalCount) I get the error Cannot implicitly convert type 'int?' to 'int'. An explicit conversion exists (are you missing a cast?) which leads me to believe that there is something wrong with my LINQ query.

The TopOwner model class looks like this:

public class TopOwner {
  public string OwnerName;
  public int CalculatedOwnerRentalCount;
  public int OwnerId;
}

Can anyone spot what I'm doing wrong with my LINQ query? It will be much appreciated.

Mitch
  • 715
  • 4
  • 12
  • 28
  • `x.CalculatedOwnerRentalCount.GetValueOrDefault()` so that `0` is used instead of `NULL` – Camilo Terevinto May 25 '19 at 11:47
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? – NetMage May 28 '19 at 23:27

1 Answers1

0

Apparently, you're trying to Max on a list of int? (Nullable ints).
And you're output class TopOwner's CalculatedOwnerRentalCount is of type int.
So, you have a casting problem - from int? to int, as the compilter hints you.

eyal
  • 369
  • 1
  • 5
  • 14