CODEWITHSUNDEEP
c#linq-to-sql
frenchie
frenchie

Reputation: 51937

extracting list of longs from database

I have an object that contains a list of longs. In the database, I'm storing this as a string separated with commas. Whenever I do a read/write operation, I'm always exploding or joining this string and I'm looking to move this step from the business logic to the queries. So this is what I tried with my query:

var TheData = (from l in MyDC.Table
               ....
               select new MyModel()
               {
                   TheListOfLongs = (from x in l.StringOfLongs.Split(',')
                                     select Convert.ToInt64(x)).ToList<long>(),

                    SomeObjectProp = ...

               }).ToList();

The error I'm getting is this:

Method 'System.String[] Split(Char[])' has no supported translation to SQL.

How can I fix my query to make it work?

Upvotes: 1

Views: 93

Answers (2)

Josh E
Josh E

Reputation: 7432

you're attempting to execute the Split operation on the DB, which replies by saying 'I have no idea how to do what you just asked me to do!'.

The solution is to call .ToList() before you select new Model() EDIT: rework and modifications for deferment - accidentally moved split to wrong place - should be right now

var TheData = (from l in MyDC.Table select new { 
    stringLong = l.StringOfLongs,
    OtherProps = //...
})
  .AsEnumerable()
  .Select(x => new Model 
                   { 
                       TheListOfLongs = x.stringLong 
                              .Split(',')   
                              .Select(y => Convert.ToInt64(y)
                              .Cast<long>(),
                       OtherProps = // ...
                   });

If you want this all in query syntax, it would look like so:

var TheData = (from l in MyDC.Table select new { 
    stringLong = l.StringOfLongs,
    OtherProps = //...
}).AsEnumerable();

var RealTheData = (from l in TheData select new Model 
    {
        TheListOfLongs = (from s in l.stringLong 
                              .Split(',') select Convert.ToInt64(s).Cast<long>(),
                       OtherProps = // ...
    });

Upvotes: 3

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236218

var TheData = MyDC.Table
                  .Where(...)
// optionally     .Select(l => new { l.StringOfLongs, l.SomeObjectProp })
                  .AsEnumerable() // move execution to memory
                  .Select(l => new Model() {
                      TheListOfLongs = l.StringOfLongs.Split(',')
                                        .Select(x => Int64.Parse(x))
                                        .ToList(),
                      SomeObjectProp = l.SomeObjectProp
                  }).ToList();

Upvotes: 2

Related Questions