2

Inspect the following code: 

public IEnumerable<Query> GetAllQueries_FilteredOnCurrentUsersInvolvement(User user)
{
    var queries = _genericUnitOfWork.GenericRepository<Query>().Get(q =>
        q.AuthorUserID == user.ID ||
        q.ConsultantUserID == user.ID ||
        q.CreatorUserID == user.ID ||
        q.EngagementPartnerUserID == user.ID ||
        q.EQCRPUserID == user.ID ||
        q.LeadPartnerUserID == user.ID ||
        q.RMPUserID == user.ID ||
        (!string.IsNullOrEmpty(q.OthersInvolvedUserIDs) && q.OthersInvolvedUserIDs.Split(',').Contains(user.ID.ToString())),
        includeProperties: "NatureOfQuery");
    return queries;
}

It compiles just fine. But when you run the code:

{"LINQ to Entities does not recognize the method 'System.String[] Split(Char[])' method, and this method cannot be translated into a store expression."}

I could write the T-SQL code myself. How to make LINQ to Entities understand how to process this?

P.S> Would this nuget package be able to resolve this?

The suggested duplicate link, does not provide any answer how to resolve this issue. So no that did not answer this question.

Tony_KiloPapaMikeGolf
  • 879
  • 12
  • 28
  • 1
    I am also reading this now: http://stackoverflow.com/questions/23929801/linq-to-entities-does-not-recognize-the-method-system-string-splitchar-m – Tony_KiloPapaMikeGolf Feb 24 '17 at 10:07
  • @wkl Same error, however the solution cannot be applied here. – Rob Feb 24 '17 at 10:14
  • I think you have to resort to using `q.OthersInvolvedUserIDs.Contains(user.ID.ToString())` in the expression as a rough preselection and then refine the results in memory by checking against the splitted user ids. – Gert Arnold Feb 24 '17 at 10:25
  • 1
    Not a solution to your immediate problem, but it's a bit suspect to store foreign keys in a CSV column. Why not design your table to properly implement n to many? – Rob Feb 24 '17 at 10:25
  • The current duplicate is better than the previous one, but the solution is not correct. If `OthersInvolvedUserIDs` = `21,31` and `UserID = 1` there will be a false positive. Conversely, when `OthersInvolvedUserIDs` = `21` and `UserID = 21` there will be a false negative. – Gert Arnold Feb 24 '17 at 11:14
  • 1
    @rob: I did not create the database. But I have consulted management here. And I got permission to change the database. I will alter the database. Because it it ridiculous to store FK's as a CSV column. – Tony_KiloPapaMikeGolf Feb 24 '17 at 11:18

1 Answers1

0

The Split operation is send along with the query and therefore needs to be translated to an SQL function. This is not implemented and therefore doesn't work. You need to retrieve the data and perform the split in memory.

Mixxiphoid
  • 1,044
  • 6
  • 26
  • 46