CODEWITHSUNDEEP
c#sql-serverlinqentity-framework-4.3
SeToY
SeToY

Reputation: 5895

Querying n:m-Relationships with LINQ

I got a n:m-Relationship with these three tables:

Database Model

Now I want to query all Activities, that have a list of selected Characteristics attached.

My query:

e.QueryableSource = _dataContext.Activities.Where(ac => 
                ac.UserId == _userId && 
                ac.ResubmissionDate <= EntityFunctions.TruncateTime(_to) && 
                ac.Priority <= (int)_prio && 
                ac.CompletedDate == null && 
                ac.Characteristics.Contains(CharacFilter));

That sadly doesn't work, as ac.Characteristics.Contains() expects items of type ActivityCharacteristics, and not a type of Characteristic

How can I achieve that?

A side node: I don't want an exclusive filtering, say when I specify three characteristics in CharacFilter, I want to query every activity that may have one of these three characteristics (not necessarily all of them) attached.

Edit: CharacFilter is a private IEnumerable<int> CharacFilter { get; set; } It is being filled from a view where I select those characteristics and hold them in the View-Variable public List<Characteristic> SelectedCharacteristics { get; set; }

My assignment:

        var view = new AssignCharacteristicsView();
        view.ShowDialog();

        if (view.SelectedCharacteristics != null)
        {
            CharacFilter = view.SelectedCharacteristics.Select(cf => cf.Id);
        }

`

Upvotes: 2

Views: 2184

Answers (1)

Slauma
Slauma

Reputation: 177153

If CharacFilter is an IEnumerable<Characteristics>, you could try:

IEnumerable<int> charaFilters = CharacFilter.Select(cf => cf.Id);

e.QueryableSource = _dataContext.Activities.Where(ac => 
    ac.UserId == _userId && 
    ac.ResubmissionDate <= EntityFunctions.TruncateTime(_to) && 
    ac.Priority <= (int)_prio && 
    ac.CompletedDate == null &&
    ac.Characteristics.Any(acc => characFilters.Contains(acc.CharacteristicId)));

(acc is of type ActivityCharacteristics.)

And Activity.Characteristics should really have the name Activity.ActivityCharacteristics like you named it in the Characteristics entity. It's pretty confusing otherwise.

Edit

About your exception

Unable to create a constant value of type 'System.Collections.Generic.IEnumerable´1'

If CharacFilter is null you will indeed get this exception because null is a constant value that EF is unable to create. It won't throw a NullReferenceException because you are using CharacFilter in an expression. According to your edited code in the question CharacFilter could be null if view.SelectedCharacteristics is null.

To solve the problem you have to catch the null case depending on if view.SelectedCharacteristics == null means all characteristics or none in your application. If it means none I would suggest to instantiate an empty list:

if (view.SelectedCharacteristics != null)
{
    CharacFilter = view.SelectedCharacteristics.Select(cf => cf.Id);
}
else
{
    CharacFilter = new List<int>();
}

If it means all characteristics you could try:

// ...
ac.Characteristics.Any(acc => (characFilters != null
    ? characFilters.Contains(acc.CharacteristicId)
    : true)));

Edit 2

Last option throws the same exception (not surprising because to compare CharacFilter with null a constant value must be created again). Alternative (just omit the last Where clause if CharacFilter is null):

ObjectQuery<Activity> query = _dataContext.Activities.Where(ac => 
    ac.UserId == _userId && 
    ac.ResubmissionDate <= EntityFunctions.TruncateTime(_to) && 
    ac.Priority <= (int)_prio && 
    ac.CompletedDate == null);

if (characFilters != null)
{
    query = query.Where(ac => ac.Characteristics
                     .Any(acc => characFilters.Contains(acc.CharacteristicId)));
}

e.QueryableSource = query;

Upvotes: 4

Related Questions