CODEWITHSUNDEEP
c#.netentity-frameworklinq-to-sql
Nullius
Nullius

Reputation: 2702

Linq-to-sql query int array in entity

Let's say we have a class that has an int array as one of its properties.

public MyClass
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int[] Values { get; set; }
}

We store it in the db using entity framework. What I've noticed is that EF turns the Values property into a varchar(max).

Now, what we would want to do, is to query the database and return records for which 'Values' contains the given int parameter.

public List<MyClass> Search(int valueToSearch)
{
    return context.MyClasses.Where(x => x.Values.Contains(valueToSearch)).ToList();
}

However, this throws an exception that linq-to-sql does not support the contains command.

I also tried Where(x => x.Values.Any(y => y == valueToSearch)) but that throws the same exception.

I suppose it has something to do with the fact that EF turns the array into a varchar so it won't be able to use the contains statement or something. Maybe a seperate table for the Values would solve the problem, but it looks kinda stupid to create a class that only has one integer property?

Is there a better way to solve this?

I found a lot of examples doing the opposite (the SQL IN statement) but that's not what we're looking for. We only have one integer as parameter, but several integers in our entity.

We're using EF5 with .NET 4.0.

Thanks!

Edit

It seems the varchar is a string we create ourselves. As it was code I didn't write myself, I wasn't aware of that. That string ofcourse gets translated into a varchar.

So now the question changes into something like 'What's the best way to store arrays of primitive types?' and that question has already been answered many times here on SO (one of them is provided in the comments).

Upvotes: 0

Views: 2515

Answers (1)

Sam Leach
Sam Leach

Reputation: 12956

Do the query outside of linq to entities: (you have to pull in all the rows)

public List<MyClass> Search(int valueToSearch)
{
    var c = context.MyClasses.ToList(); // cache
    return c.Where(x => x.Values.Contains(valueToSearch));
}

EDIT: If you are currently manually converting the array of integers into a string, then change your class property from an array of integers to a string.

Although, I recommend a new table. An array of integers as a string in a db fields smells a little.

Upvotes: 1

Related Questions