0

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

Jämes
  • 6,945
  • 4
  • 40
  • 56
Nullius
  • 2,607
  • 2
  • 16
  • 22
  • Well there's a parenthesis mismatch for starters (should be a closing bracket after valueToSearch)). –  Sep 25 '13 at 13:45
  • 4
    Very interesting. Can you show how you map integer array to database? – Sergey Berezovskiy Sep 25 '13 at 13:46
  • 2
    I think you need to create a separate table for `Values`. – Sam Leach Sep 25 '13 at 13:48
  • 1
    So it's LINQ to Entities, not LINQ to SQL. – Roy Dictus Sep 25 '13 at 13:49
  • 1
    @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 Sep 25 '13 at 13:52
  • 1
    Check this post http://stackoverflow.com/questions/11985267/entity-framework-options-to-map-list-of-strings-or-list-of-int-liststring – Esteban Elverdin Sep 25 '13 at 13:52
  • I agree with @SamLeach - this looks like a one-to-many relationship, so you'll need to create another table to store the values – Rui Jarimba Sep 25 '13 at 13:54

1 Answers1

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.

Sam Leach
  • 12,746
  • 9
  • 45
  • 73
  • 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. – MBentley Feb 26 '16 at 18:04
  • True. Post an answer with your solution. – Sam Leach Feb 26 '16 at 18:16