Linq-to-sql query int array in entity

Question

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).

Very interesting. Can you show how you map integer array to database? — Sergey Berezovskiy
– Sergey Berezovskiy, Commented Sep 25, 2013 at 13:46
@lazyberezovsky Darn, after your comment I looked at the class itself, where apparently we create the string ourselves. It's code I didn't write myself so I wasn't aware of it. I just looked how it was stored in the database. Anyway, I'll edit my question. — Nullius
– Nullius, Commented Sep 25, 2013 at 13:52

Sam Leach · Accepted Answer · 2013-09-25 13:52:41Z

1

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.

answered Sep 25, 2013 at 13:52

Sam Leach

13k10 gold badges49 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MBentley Over a year ago

This will work great for smaller data sets but what about when the client gets more data. I have a project where it worked during initial development, but once the client got to 3.8 million items this blew up and we had to filter the data down before loading it to make sure that we just got the data that we wanted.

Sam Leach Over a year ago

True. Post an answer with your solution.

Collectives™ on Stack Overflow

Linq-to-sql query int array in entity

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related