CODEWITHSUNDEEP
c#linqlinq-to-sqlexpression-trees
AllmanTool
AllmanTool

Reputation: 1554

LINQ TO SQL Replace method

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.

Upvotes: 0

Views: 2436

Answers (2)

MBoros
MBoros

Reputation: 1140

Use LinqKit, and write:

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

Upvotes: 1

Harald Coppoolse
Harald Coppoolse

Reputation: 30512

Am I right that your problem is that an IQueryable can't use any local functions and can't translate all standard LINQ methods to SQL as is described in supported and unsupported LINQ methods?

I'd go for AsEnumerable. AsEnumerable will bring the input to local memory so that you can call any local function you want.

As it seems that the result of your query is expected to be only one element, it is not a problem if you transport the complete tableName to local memory before you convert it into an OriginalTableName

var result = dbContext.Repository<SomeTable>()
    ...
    .Where(someTableElement => someTableElement.Id == 1)
    .Select(tableItem => new
    {
        TableId = tableItem.TableId,
        TableName = tableIem.TableName,
    })
    .AsEnumerable()

    // from here, all elements (expected to be only one) are in local memory
    // so you can call:
    .Select(localItem => new
    {
        TableId = localItem.TableId,
        OriginalTableName = localItem.TableName.Replace("$", "_")
    })
    .SingleOrDefault();

Be careful when using AsEnumerable. Try not to transport a lot of data to local memory that you won't use. So try to perform Join / Where / Select as much as possible while AsQueryable. Only if you've limited your data to what you really plan to use, move it to local memory

Upvotes: 0

Related Questions