LINQ TO SQL Replace method

Question

I've code, it works fine.

 using (var dbContext = new UnitOfWorkFactory(sqlConnection).Create())
 {
        var result = dbContext.Repository<SomeTable>()
            .Get()
            .AsNoTracking()
            .Where(r => r.Id == 1)
            .Select(item => new
            {
                TableId = item.TableId,
                OriginalTableName = item.TableName.Replace("$", "_")
            })
            .SingleOrDefault(); 

When I try to replace logic in seperate private method I get exception. I understand that main reason is that LINQ to SQL provider can't translate clr method to SQL.

...
.Select(item => new
 {
   TableId = item.TableId,
   OriginalTableName = SubQueryReplace(item.TableName)
 })
...

Actually I guess that I have to use Expression tree, but can't resolve how I have to write it. When I try return Expression<Func<string>> from SubQueryReplace method CLR compiler unhappy, but when I try to do something like

private Expression<Func<string, string>>SubQueryReplace(string fieldValue)
{
   Expression<Func<string, string>> exp = (tableName) => tableName.Replace("D", "_");`
   return exp
}

...
.Select(item => new
 {
   TableId = item.TableId,
   OriginalTableName = SubQueryReplace.Compile.Invoke(item.TableName)
 })
...

LINQ to Sql doesn't understand what I want from it .

So as you can see I'm confused. Please help to solve this syntactic task.

Answer 1

Use LinqKit, and write:

...
.AsExpandable()
.Select(item => new
 {
   TableId = item.TableId,
   OriginalTableName = SubQueryReplace(item.TableName).Expand()
 })
...