CODEWITHSUNDEEP
c#.netlinqentity-framework-4
gregwhitworth
gregwhitworth

Reputation: 2178

LINQ Filter List inside another LINQ Query

Ok, so I have a model that looks like this:

public int idA
public int idB
public int total

public virtual TableA TableA { get; set; }
public virtual TableB TableB { get; set; }

The models for Table A and B are similar to each other, they both tie to this with something like

public virtual List<Association> Assocation { get; set; }

I am now trying to query this, and it is working but I want to be able to filter the results when idB equals a certain integer, for example:

var results = db.TableA
                .Where(t => t.idA == id)
                .Where(t => t.Association.Where(m => m.idB == 1));

This returns the following exceptions:

Cannot implicitly convert to 'bool' Cannot convert lambda expression to delegate type 'System.Func' because some of the return types in the block are not implicitly convertible to the delegate return type

Thank you so much for your help!

Update

So I have implemented the following:

var results = db.TableA
                .Where(t => t.idA == id)
                .Where(t => t.Association.Any(m => m.idB == 1));

Since this association table uses a compound primary key there should only be 1 result returned. There are about 200 results that match the given t.idA == id and that is what I am getting back. It is not only returning the 1 result.

Just for thoroughness here is the query being created, I omitted the fields themselves to simplify it some:

SELECT ... fields here ...
WHERE ([Extent1].[id] = @p__linq__0) AND (@p__linq__0 IS NOT NULL) 
AND ( EXISTS (SELECT ... fields here ....
                WHERE ([Extent1].[id] = [Extent2].[idA]) AND (1 = [Extent2].[idB])
              )
     )

Update 2

So the problem with .Any() is it will return the entire collection IF it contains a value that matches 1, all I was wanting was it to return the value that matches 1. So because of this, the only thing I could think of to do was to take the extra 118 rows and then filter the list returned. Luckily upon profiling, this hasn't impacted the SQL server as I initially expected so it was not necessary to do pre-optimization. However, if anyone does know how to filter a list within the initial SQL query using LINQ I would still love to know as I'm sure I could use this in the future where the impact on the database may be more severe, thus the optimization warranted.

Upvotes: 2

Views: 5695

Answers (3)

p.s.w.g
p.s.w.g

Reputation: 148990

I think what you want to do is use the Any method. Something like this should work:

var results = db.TableA
                .Where(t => t.idA == id)
                .Where(t => t.Association.Any(m => m.idB == 1));

This will return any Table with the idA equal to id and at least on Association with idB equal to 1.

Or if you prefer query syntax:

var results = 
    from a in TableA
    where a.idA == id && a.Association.Any(m => m.idB == 1)
    select a;

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

This should work:

var results = db.TableA
    .Where(t => t.idA == id)
    .Select(t => new {
        Item = t
    ,   AssociatedItem = t.Association.SingleOrDefault(m => m.idB == 1)
    })
    .Where(p => p.Associated != null)
    .ToList();

This code produces a list of objects of anonymous type with two fields - the Item field with the item, and the AssociatedItem containing its associated item that has idB of 1.

Note that the above code assumes that there is at most one associated item with idB of 1; otherwise, SingleOrDefault would fail.

Upvotes: 0

Maximum Cookie
Maximum Cookie

Reputation: 437

The '.Where' lambda function needs to return a boolean value. Currently, you are returning the results of a '.Where' against another recordset. Instead, you probably intended something like this:

var results = db.TableA
                .Where(t => t.idA == id)
                .Where(t => t.Association.Any(m => m.idB == 1));

The '.Any' function will return true if 't.Association' contains any records that match the condition.

Upvotes: 4

Related Questions