How to get list of ViewModel from database?

Question

I have a view model class for getting a list of Clients with their list of offers.

Restaurant Viewmodel

public class RestaurantsListVM
{
    public Client client { get; set; }
    public List<Offer> offers { get; set}; 
}

Client Model

public class Client
{
     public Guid Id { get; set; }
     public String RestaurantName { get; set; }
}

Offer Model

public class Offer
{
     public Guid Id { get; set; }
     public String OfferName { get; set; }
     public decimal OfferPercentage { get; set; }

In my database , I have a ClientOffer Table that also maps Clients with their offers like :

***"ClientOfferId,ClientId,OfferId"***

So I created this function to retrieve data from the database.

public List<RestaurantsListVM> GetRestaurants()
{
     List<RestaurantsListVM> restaurantlist = new List<RestaurantsListVM>();

     var clients = new Client().GetClients();

     foreach (Client c in clients)
     {
         RestaurantsListVM restaurantdetails = new RestaurantsListVM();
         restaurantdetails.client = c;
         restaurantdetails.offers = new Offer().GetOffers(c.Id);
         restaurantlist.Add(restaurantdetails);
     }

     return restaurantlist;
 }

It is working fine. But the problem is it is executing query again and again in the sql server while retrieving each and every client offer, and the performance goes down.

How should I improve my code efficiency for better performance ?

Answer 1

You need a LINQ query that will join the tables in one SQL query. Currently you get all the clients and then for each client get their offers. Something like:

var clientOffers = (from cli in dbContext.Clients

join cli_ofr in dbContext.ClientOffer
on cli.ClientId
equals cli_ofr.ClientId

join ofr in dbContext.Offers
on cli_ofr.OfferId
equals ofr.OfferId

select new {
Client = new Client { Guid = cli.Guid...},
Offer = new Offer { Guid = ofr.Guid... }
}).toList();

This will generate a SQL query which will return all the data you need to create your view model.