LINQ to Entities does not recognize the method IsUserInCc

Question

My Code:

elections = elections.Where(e => e.Creator == Username || e.Approver == Username || IsUserInCc(e.Cc,Username))
    .OrderBy(e => e.Status)
    .ThenByDescending(e => e.Group);
var test = elections.FirstOrDefault();

private bool IsUserInCc(string cc, string username)
{
    var ccList = cc.Split(';');
    if (ccList.Contains(username))
        return true;
    return LDAPUtility.Instance.IsUserInGroup(ccList.ToList(), username);
}

Error:

LINQ to Entities does not recognize method IsUserInCc.

From many posts, I can understand why error was thrown. Basically IsUserInCc is not available in SQL execution. I need somehow convert it back to C# to handle it.

LINQ to Entities does not recognize my method

LINQ to Entities does not recognize the method in query

LINQ to Entities does not recognize the method 'System.String ToString(Int32)'

However, in my specific case, what is the best approach?

what is the purpose of LDAPUtility.Instance.IsUserInGroup(ccList.ToList(), username);? if that can be removed or reworked the query can probably be compatible with IQueryable — Mel Gerats, Aug 06 '18 at 07:26

score 2 · Answer 1 · answered Aug 06 '18 at 07:07

You need to convert to list first. Also note that elections must be able to hold a list for this to run.

elections = elections.ToList().Where(e => e.Creator == Username || e.Approver == Username || IsUserInCc(e.Cc,Username))
.OrderBy(e => e.Status)
.ThenByDescending(e => e.Group);

score 1 · Answer 2 · edited Aug 06 '18 at 07:40

1

For your function written in code, you cannot use that on Queryables. You need to convert to in-memory list and then apply the filter required using your function.

edited Aug 06 '18 at 07:40

Lyco

549
3
14

answered Aug 06 '18 at 07:01

Sarav

139
1
13

So what if `elections` contains a million records? – Gert Arnold Aug 06 '18 at 08:03
Then you need to convert whatever you are doing in this custom function to SQL function . No other go to handle this – Sarav Aug 06 '18 at 08:05
But that's impossible because of the `string.Split`. I don't think there is any feasible way along the lines of "fetch first, then filter". OP should change the data structure, as suggested [in this answer](https://stackoverflow.com/a/51702878/861716). – Gert Arnold Aug 06 '18 at 08:09
@GertArnold but that would be a design change . But if i am not wrong , the one involved here is an email Id separator (;) text box string , which cannot be normalized anymore deeper. – Sarav Aug 06 '18 at 09:23
Sure it can! The IDs shouldn't be stored as a semicolon-separated string. – Gert Arnold Aug 06 '18 at 13:39
@Gert `LDAPUtility.Instance.IsUserInGroup` looks to me a showstopper (if it is what the name implies). And since it's `Or`-ed with the `Contains` criteria, the whole criteria looks non translatable to SQL, with or w/o proper data normalization. So what the answerer suggests might be the only solution in this particular case. – Ivan Stoev Aug 07 '18 at 16:50

score 1 · Answer 3 · answered Aug 06 '18 at 07:49

The root cause of your issue is that your underlying data isn't normalised properly. You need to put your CC's in a collection, not have them as a single deliniated string.

In SQL you'd need to add a new table called CC or something and put each user name in there and link it back to an election. Or if it's an in-memory collection, add a new property that in its Getter will do the split for you.

Either way, then you won't run into this kind of problem. If your data isn't properly structured, you will create problems for yourself further up the stack.

score 0 · Answer 4 · answered Aug 06 '18 at 07:13

When you want to send request to databaseusing Linq like:

var query = listData.Where(x=>x.Id == 123);

Type of this query is IQueryable that means your query not Executed yet!

Now you are sending data as IQueryable to method and can not process on your data, you have to Execute that with methods like: Tolist(), ToListAsync() or something like these.

The best way for these is that you get data from database without that method, after that you execute your query, you can Run this method.

GoodLuck.

score 0 · Answer 5 · answered Aug 06 '18 at 07:30

Can you try like this :

 elections = elections.Where(e => e.Creator == Username || e.Approver == Username).Tolist().Where(e => IsUserInCc(e.Cc,Username))
.OrderBy(e => e.Status)
.ThenByDescending(e => e.Group);

var test = elections.FirstOrDefault();

private bool IsUserInCc(string cc, string username)
{
    var ccList = cc.Split(';');
    if (ccList.Contains(username))
        return true;
    return LDAPUtility.Instance.IsUserInGroup(ccList.ToList(), username);
}

This is different: it turns the last `||` into an `&&`. – Gert Arnold Aug 06 '18 at 08:05 — Gert Arnold, Aug 06 '18 at 08:05

LINQ to Entities does not recognize the method IsUserInCc

5 Answers5