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.

Because there is a lot a dublicate functionality . Many diffrent method use same logic. I really think that i have to move it in separate method or ever implement specification pattern and moving all dublicate logic in specifications — AllmanTool
– AllmanTool, Commented Mar 6, 2018 at 7:31
Somethink like linq to sql doesn't supported SubQueryReplace method — AllmanTool
– AllmanTool, Commented Mar 6, 2018 at 7:36
@AllmanTool Replace method can translate to sql in entityframework version 6.2 — Eldaniz Ismayilov
– Eldaniz Ismayilov, Commented Mar 6, 2018 at 10:15

MBoros · Accepted Answer · 2018-03-08 07:02:01Z

1

Use LinqKit, and write:

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

answered Mar 8, 2018 at 7:02

MBoros

1,1307 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harald Coppoolse · Accepted Answer · 2018-03-06 07:56:52Z

0

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

answered Mar 6, 2018 at 7:56

Harald Coppoolse

30.6k7 gold badges78 silver badges138 bronze badges

1 Comment

AllmanTool Over a year ago

Thx for your attention, but the main reason of my quistion was to work with Iqueryble in more convenient way. Generaly linq replace function work within Iqueryble and doesn't work in private method outside Iqueryble

Collectives™ on Stack Overflow

LINQ TO SQL Replace method

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related