Using custom methods in LINQ query

Question

I have this piece of code:

int chunkSize = 1000;
var chunkQueryBase = _sdb.Entities.Where(m => FirstNumberGiver(m) < SecondNumberGiver(m)).OrderBy(c => c.Id);
var chunkQuery = chunkQueryBase.Take(chunkSize);

var x = chunkQuery.ToString();

var chunks = chunkQuery.ToList();

For example FirstNumberGiver methods is like this:

public int FirstNumberGiver(Entity entity)
{
    string numberString = entity.Number;

    string numpart1= "";
    numpart1= Regex.Match(numberString , @"\d+").Value;

    if (numpart1!= "")
    {
        return (Int32.Parse(numpart1));
    }
    else return 0;
}

The SecondNumberGiver function is very similar to FirstNumberGiver. The code gives exception at this line :

var chunks = chunkQuery.ToList();

The exception is this:

LINQ to Entities does not recognize the method 'Int32 FirstNumberGiver(Entity)' method, and this method cannot be translated into a store expression.

Is there a solution for this problem? How can I get rid of this error?

Remember that LINQ to Entities has to try to translate your query into SQL, so you're getting the exception because it has no idea what to do with your custom function. — DavidG, Mar 09 '17 at 11:12
Related : http://stackoverflow.com/questions/41802366/get-result-function-in-linq-without-translate-to-store-expression — Salah Akbari, Mar 09 '17 at 11:13
Your function appears to only be converting the string to a number. This is usually a big hint that you're storing your data in the wrong format. Are you able to fix the source data? — DavidG, Mar 09 '17 at 11:17
@DavidG, No, unfortunately that field must be string. I can't do changes. — jason, Mar 09 '17 at 11:23

Liviu Boboia · Answer 1 · 2017-03-09T11:16:51.947

0

Your code generates an SQL query that uses the function FirstNumberGiver and SecondNumberGiver of which SQL does not know about, the easiest fix(but the worst performance wise) would be to get all the entities in the memory using _sdb.Entities.ToList() and filter them after

var chunkQueryBase = _sdb.Entities.ToList().Where(m => FirstNumberGiver(m) < SecondNumberGiver(m)).OrderBy(c => c.Id);

edited Mar 09 '17 at 11:16

answered Mar 09 '17 at 11:13

Liviu Boboia

1,734
1
10
21

1

This would cause the entire table to be loaded into memory, potentially a terrible idea! – DavidG Mar 09 '17 at 11:15
Added a comment after easiest fix – Liviu Boboia Mar 09 '17 at 11:17
Easiest is often the worst though. I would never recommend to do this. – DavidG Mar 09 '17 at 11:18

score 0 · Answer 2 · answered Mar 09 '17 at 11:15

0

You can first query the data from database and then can filter it like following:

var chunkQueryBase = _sdb.Entities.ToList();
var resultSet = chunkQueryBase 
                .Where(m => FirstNumberGiver(m) < SecondNumberGiver(m))
                .OrderBy(c => c.Id);

Hope it helps :)

answered Mar 09 '17 at 11:15

Muhammad Qasim

1,622
14
26

2

This would cause the entire table to be loaded into memory, potentially a terrible idea! – DavidG Mar 09 '17 at 11:15
@DavidG - I would suggest you to create a procedure then and do your operations there. Remember, whatever linq query you make, it needs to convert it into sql and it wont be able to convert your functions. – Muhammad Qasim Mar 09 '17 at 11:17
A stored proc is one option, not one that I would choose though. – DavidG Mar 09 '17 at 11:19

Using custom methods in LINQ query

2 Answers2