CODEWITHSUNDEEP
c#linqentity-frameworklambda
Karthikeyan
Karthikeyan

Reputation: 319

One to many linq query

I am trying to fetch list of customers and theirs orders which are not deleted state.

I have a Customer entity

public class Customer
{
  public int Id {get;set;}
  public string Name {get;set;}
  public Collection<Order> Orders {get;set;}
}

And I have a order entity like,

public class Order
{
  public int Id{get;set;}
  public string Name {get;set;}
  public decimal Cost {get;set;}
  public int CustomerId {get;set;} 
  public Customer Customer {get;set;}
  public bool IsDeleted {get;set;}
}

Here I am struggling to fetch the list of customers and theirs orders which are not deleted.

What I have done to achieve the above requirement is,

var customers = (from customer in Customers
                     join order in Orders.Where(x=>!x.IsDeleted)
                         on customer.Id equals order.CustomerId
                  where !order.IsDeleted).ToList()

I am getting list of customers with all their orders(including deleted = 1) when I run this query.

Please help me out to ignore the Deleted orders.

Thanks in advance.

Upvotes: 1

Views: 192

Answers (2)

Alex
Alex

Reputation: 13224

You need to do this differently if you want to filter the Orders of a Customer to only include those orders that were not deleted.

A new (detached) Customer instance, that is populated from the query result should then be created, or alternatively an anonymous object or specific "DTO" instance:

public class CustomerView
{
    public readonly int Id;
    public readonly string Name;
    public readonly List<Orders> Orders;
} 

var customers = 
    from customer in Customers
    select new CustomerView() {
        Id = customer.Id,
        Name = customer.Name,
        Orders = customer.Orders.Where(x=>!x.IsDeleted).ToList()
    };

Note that since there is no where clause to limit the number of returned customers you are dealing with, there is an unbounded result set as the output of this query.

If you are loading a specific customer by id, you can use the following approach to "lazy load" its orders using a filter. Note: you will need to replace the MyDbContext with your own DbContext type.

public Customer LoadById(int customerId)
{
    using (var context = new MyDbContext()) 
    {
        var myCustomer = context.Customers.Find(customerId);
        if (myCustomer != null)
        {
            // Filter orders.
            context.Entry(myCustomer)
                .Collection(c => c.Orders)
                .Query() 
                .Where(o => !o.IsDeleted)
                .Load();
        }
        return myCustomer;
    }
}

Upvotes: 1

Mark Hurd
Mark Hurd

Reputation: 10931

As far as I understand it C# requires a select clause. If you add one to your suggested query select new {customer, order} you'll see your problem. You're extracting all orders associated with a customer via customer.Orders, rather than just the results of the query.

Upvotes: 0

Related Questions