David David - 1 month ago 20
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

List<DeviceQueue>
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

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.