IQueryable with String.Format and PatIndex

Question

I am facing a issue when i try to used string.format on two db entities A, B and then using SqlFunctions.PatIndex on them

     IQueryable<Data> dataRecords = DbSet<Data> M_Data;
     dataRecords = dataRecords.Where(c => SqlFunctions.PatIndex(sqlFilter, String.Format(A,B)) > 0);

its throwing exception Linq to entities does not recognize method string.Format

When i use AsEnumarable()

dataRecords = dataRecords.AsEnumerable().Where(c => SqlFunctions.PatIndex(sqlFilter, String.Format(A,B)) > 0).AsQueryable();

than it's throwing This function can only be invoked from LINQ to Entities

Can any one suggest how to do this.

Answer 1

You have two method calls:

1. String.Format
2. SqlFunctions.PatIndex

Problem is, that the first one cannot be transformed into proper SQL query and the second one can only be executed within LINQ to Entities query context. Former makes your first attempt fail, latter breaks second try.

However, I don't see A nor B be part of the data you query. You should be able to call string.Format outside of you query and then use the result:

var formattedString = String.Format(A,B);
dataRecords = dataRecords.Where(c => SqlFunctions.PatIndex(sqlFilter, formattedString) > 0);