David David - 1 year ago 90
SQL Question

Linq To Entities - Filter on list

I'm really battling to utilize some form of filter method for some particular data. Filtering on Device (see class below) without worrying about filtering on the child

works perfectly as expected. However when I attempt to filter on say MessageId and/or SequenceId the result returns all the entries in the MessageQueue table, ignoring my filter.

See Data.Model for Device :

public class Device : EntityBase
public string SerialNumber { get; set; }
public virtual DeviceType DeviceType { get; set; }
public virtual List<DeviceQueue> MessageQueue { get; set; }

See Data.Model for DeviceQueue :

public class DeviceQueue : EntityBase
public string MessageId { get; set; }
public Int32 SequenceId { get; set; }

See Filter Method (updated following 1st suggested answer) :

public IQueryable<Device> Filter(IQueryable<Device> device)

IQueryable<DeviceQueue> deviceQueue = Enumerable.Empty<DeviceQueue>().AsQueryable();

//Because it is IQueryable, the data is not fetched until you bind it so it only pulls the data you need.
if (DeviceId != null)
device = device.Where(d => d.Id == DeviceId);

if (SequenceId != null)
device = device.Where(d => d.MessageQueue.Any(q => q.SequenceId == SequenceId.Value));

if (MessageId != null)
device = device.Where(d => d.MessageQueue.Any(q => q.MessageId == MessageId));

return device;

Answer Source

You can add a predicate as follows:

 if (SequenceId.HasValue)
     device = device.Where(d => d.MessageQueue
                        .Any(q => q.SequenceId == SequenceId.Value));

Same with MessageId.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download