Linq dynamically Add Where conditions

Question

Linq dynamically Add Where conditions

Cenk 766

Hi,

I am getting values from various form inputs and trying to dynamically construct the where clause. I couldn't find a way to, unfortunately. Hope you can help.

Here is

public async Task<IEnumerable<Order>> GetOrdersForExport(int vendorId,string status, DateTime? startDateTime, DateTime? endDateTime)  
        {  
            var result = from order in _db.Orders  
                from orderDetail in _db.OrdersDetail on order.Id equals orderdetail.OrderId  
                from vendor in _db.Vendors on orderdetail.VendorId equals vendor.Id  
               select new {order, orderdetail, vendor} ;  
  
            if (vendorId != null)  
            {  
                  
            }  

            return await result;  
        }

Here is the relations

Karen Payne MVP 30,126 Reputation points

2022-08-21T17:33:43.897+00:00

What are all known possible conditions you are looking for?

Perhaps ExpressionGenerator might be of interest.

Viorel 88,566

Do you mean something like this?

if( vendorId != 0)  
{  
   result = result.Where( d => d.vendor.Id = vendorId);  
}  
  
if( ! string.IsNullOrWhitespace( status))  
{  
   result = result.Where( d => d.order.Status == status);  
}  
  
if( startDateTime != null)  
{  
   result = result.Where( d => d.orderDateTime >= startDateTime);  
}  
  
and so on...

Cenk 766 Reputation points

2022-08-21T19:07:25.09+00:00

Hi @Viorel , thank you for your reply.

When I write it in the linqpad it gives an error. Is it related with linqpad?
Karen Payne MVP 30,126 Reputation points

2022-08-21T22:00:41.213+00:00

I got rid of my first reply was having issues with the editor

Cenk 766

Hi @Karen Payne MVP , Thank you for your help. I changed the query to method linq in order to return Task in my method. But the problem is how to get vendor in the where clause since OrderDetails is a list.

public async Task<IEnumerable<Order>> GetOrdersForExport(int vendorId,string status, DateTime? startDateTime, DateTime? endDateTime)  
        {  
            var result = _db.Orders  
                .Join(  
                    _db.OrdersDetail,  
                    order => order.Id,  
                    orderdetail => orderdetail.OrderId,  
                    (order, orderdetail) =>  
                        new  
                        {  
                            order = order,  
                            orderdetail = orderdetail  
                        }  
                )  
                .Join(  
                    _db.Vendors,  
                    temp0 => temp0.orderdetail.VendorId,  
                    vendor => vendor.Id,  
                    (temp0, vendor) => temp0.order  
                        
                );   
  
  
            if (vendorId != null)  
            {  
                result = result.Where(d => d.OrderDetail)  
            }  
            return await result.ToListAsync();  
        }

Cenk 766 Reputation points

2022-08-22T05:35:04.67+00:00
Does this work?

result = result.Where(d => d.OrderDetails.Any(x => x.VendorId == vendorId));
Cenk 766 Reputation points

2022-08-22T06:24:11.403+00:00

Because of joining with OrderDetails, the result increases the data. Notice the Ids in the picture. How can I retrieve without duplicating data?
Jack J Jun 21,976 Reputation points Microsoft Vendor

2022-08-23T07:57:09.017+00:00

@Cenk , If you want to retrieve without duplicating data, you could try to use Distinct method to do it.

Karen Payne MVP 30,126 Reputation points

2022-08-21T17:33:43.897+00:00

What are all known possible conditions you are looking for?

Perhaps ExpressionGenerator might be of interest.
Viorel 88,566 Reputation points

2022-08-21T18:19:34.613+00:00

Do you mean something like this?

if( vendorId != 0) { result = result.Where( d => d.vendor.Id = vendorId); } if( ! string.IsNullOrWhitespace( status)) { result = result.Where( d => d.order.Status == status); } if( startDateTime != null) { result = result.Where( d => d.orderDateTime >= startDateTime); } and so on...
Cenk 766 Reputation points

2022-08-21T19:07:25.09+00:00

Hi @Viorel , thank you for your reply.

When I write it in the linqpad it gives an error. Is it related with linqpad?
Karen Payne MVP 30,126 Reputation points

2022-08-21T22:00:41.213+00:00

I got rid of my first reply was having issues with the editor
Cenk 766 Reputation points

2022-08-22T05:28:17.38+00:00

Hi @Karen Payne MVP , Thank you for your help. I changed the query to method linq in order to return Task in my method. But the problem is how to get vendor in the where clause since OrderDetails is a list.

public async Task<IEnumerable<Order>> GetOrdersForExport(int vendorId,string status, DateTime? startDateTime, DateTime? endDateTime) { var result = _db.Orders .Join( _db.OrdersDetail, order => order.Id, orderdetail => orderdetail.OrderId, (order, orderdetail) => new { order = order, orderdetail = orderdetail } ) .Join( _db.Vendors, temp0 => temp0.orderdetail.VendorId, vendor => vendor.Id, (temp0, vendor) => temp0.order ); if (vendorId != null) { result = result.Where(d => d.OrderDetail) } return await result.ToListAsync(); }
Cenk 766 Reputation points

2022-08-22T05:35:04.67+00:00

Does this work?

result = result.Where(d => d.OrderDetails.Any(x => x.VendorId == vendorId));
Cenk 766 Reputation points

2022-08-22T06:24:11.403+00:00

Because of joining with OrderDetails, the result increases the data. Notice the Ids in the picture. How can I retrieve without duplicating data?
Jack J Jun 21,976 Reputation points Microsoft Vendor

2022-08-23T07:57:09.017+00:00

@Cenk , If you want to retrieve without duplicating data, you could try to use Distinct method to do it.

Linq dynamically Add Where conditions

Activity