How to split string in c# linq?

Question

I have this

from d in db.v_Report_CompanySearches
orderby d.InquiryLogID descending
where (mPersonName == null || d.AccountName.ToLower() == mPersonName || d.PersonName.ToLower() == mPersonName) && 
      (mCompanyName == null || TagsContain(d.CompanySearchTerm, mCompanyName)) && 
      d.CreateDT >= mFrom && d.CreateDT <= mTo
select (d);

and

private bool TagsContain(string terms, string val)
{
    string[] tags = terms.ToLower().Split(';');
    return tags.Contains(val.ToLower());
}

but it crashes with not supported error. I think it's because I'm using a custom function TagsContain. How can I do that function in linq without custom stuff?

Thanks

Possible duplicate of [LINQ to Entities: Why can't I use Split method as condition?](https://stackoverflow.com/questions/1354723/linq-to-entities-why-cant-i-use-split-method-as-condition) — Loofer, Oct 05 '18 at 21:52
The duplicate is good, however it wont pick up a list with one element and unless its terminated by the seperator — TheGeneral, Oct 05 '18 at 21:55
Any solution will merely patch up poor design: you should split the tags in the database as a 1:n relationship. This is never going to work smoothly. — Gert Arnold, Oct 07 '18 at 09:43

Austin T French · Answer 1 · 2018-10-05T22:21:06.483

0

Id TagsContain isn't supported by EF and have an underlying SQL function, it will crash. That's exactly what is happening here.

This however, should work:

from d in db.v_Report_CompanySearches
orderby d.InquiryLogID descending
where (mPersonName == null || d.AccountName.ToLower() == mPersonName || d.PersonName.ToLower() == mPersonName) && 
      (mCompanyName == null || d.CompanySearchTerm.Contains(mCompanyName)) && 
      d.CreateDT >= mFrom && d.CreateDT <= mTo
select (d);

edited Oct 05 '18 at 22:21

answered Oct 05 '18 at 21:47

Austin T French

5,022
1
22
40

I get this `LINQ to Entities does not recognize the method 'System.String[] Split(Char[])' method, and this method cannot be translated into a store expression.` – omega Oct 05 '18 at 21:49
1

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/string-canonical-functions `split` is not a supported function – TheGeneral Oct 05 '18 at 21:57
@Saruman you're correct! I thought I double checked that, in hindsight though, just .Contains("SearchWord") should work – Austin T French Oct 05 '18 at 22:25

score 0 · Answer 2 · answered Oct 05 '18 at 22:36

Provider not able convert your custom function the sql. And I afraid split is one of the functions which not supported for generating sql.

You can use it without .Split

var query = 
    db.v_Report_CompanySearches
      .Where(report => report.CreateDT >= from)
      .Where(report => report.CreateDT <= to);

if (String.IsNullOrEmpty(personName) == false)
{
    query = query.Where(report => report.AccountName.ToLower() == personName || 
                                  report.PersonName.ToLower() == personName);
}

if (String.IsNullOrEmpty(companyName) == false)
{
    query = query.Where(report => report.CompanySearchTerm.StartsWith($"{companyName};") ||
                                  report.CompanySearchTerm.Contains($";{companyName};")) ||
                                  report.CompanySearchTerm.EndsWith($";{companyName}"))
}

var result = query.OrderByDescending(report => report.InquiryLogID).ToList();

score 0 · Answer 3 · answered Oct 07 '18 at 09:25

What Fabio said is right. Split function of c# can not be converted into SQL query. So, you have one way here

Get all the values from DB into C# List object and then apply the split filter over it.

var myListObject = (from d in db.v_Report_CompanySearches
orderby d.InquiryLogID descending
where (mPersonName == null || d.AccountName.ToLower() == mPersonName || d.PersonName.ToLower() == mPersonName) && 
      d.CreateDT >= mFrom && d.CreateDT <= mTo
select (d)).ToList();

Then

var afterFilterObject = myListObject.Where(d => (d.mCompanyName == null || TagsContain(d.CompanySearchTerm, mCompanyName))).ToList();

Method to be called

private bool TagsContain(string terms, string val)
{
    string[] tags = terms.ToLower().Split(';');
    return tags.Contains(val.ToLower());
}

How to split string in c# linq?

3 Answers3