CODEWITHSUNDEEP
c#linqlinq-to-sql
Himberjack
Himberjack

Reputation: 5792

C# Linq Contains statement

I wrote my own object Tag and I would like to to contains if the .Value is found (I want to simulate WHERE IN like in SQL)

public static List<Question> GetQuestionsIdsWithTags(List<Tag> tags)
        {
    IEnumerable<Question> res = from t in dataClasses.tags
                                    join
                                    qt in dataClasses.question_to_tags on t.id equals qt.tag_id
                                    join q in dataClasses.questions on qt.question_id equals q.id
                                    where tags.Contains<Tag>(new Tag(t.name))
                                    select new Question(q.text) { };

problem is, if the Contains is in the query, I get

The member 'Core.Literal.Value' has no supported translation to SQL.

Where Literal is the base of Tag.

What can I do?

Upvotes: 2

Views: 4148

Answers (3)

Marc Gravell
Marc Gravell

Reputation: 1062745

if tags is a List<string>, you should find that:

where tags.Contains(t.Name)

works fine; but there are limits to what it can understand (and more importantly, write as TSQL).

Upvotes: 2

Chris Wenham
Chris Wenham

Reputation: 24017

You're trying to do new Tag(t.name), but this cannot be translated into SQL (the database server can't create new instances of your Tag class). Perhaps this would work:

IEnumerable<Question> res = from t in dataClasses.tags
                            join
                            qt in dataClasses.question_to_tags on t.id equals qt.tag_id
                            join q in dataClasses.questions on qt.question_id equals q.id
                            where tags.Select(x => x.name).Contains(t.name)
                            select new Question(q.text) { };

Upvotes: 3

Prutswonder
Prutswonder

Reputation: 10064

The LinQ Contains() statement can only be translated to SQL if it is performed by a List containing basic datatypes like int or string. If you need to cast your tags list to a List of strings or ints, then it should work.

Upvotes: 1

Related Questions