Using a stored procedure in an expression

Question

I am using this Expression to create my ViewModel from Database Models:

ViewModel.cs:

 public class TestViewModel
    {
        public static Expression<Func<TestModel, TestViewModel>> Projection => dbModel => new TestViewModel
        {
            Name = dbModel.Name,
            PropertiesCommaSeparated = string.Join(", ", dbModel.TestProperties.Select(x => x.TestProperty.Value))
        };

        public string Name { get; set; }
        public string PropertiesCommaSeparated { get; set; }
    }

Since string.Join("","") is not supported in Linq to SQL this fails.

According to this answer https://stackoverflow.com/a/934668 I want to use a stored procedure.

Is it possible to execute a stored procedure in an expression like this?

I am using Entity Framework .NET Core 3.1

abdusco · Answer 1 · 2021-06-28T07:37:26.683

This is not something you want to do in a ...ViewModel. ViewModels, DTOs ideally shouldn't have any logic unrelated to views (in your case: DB operations).

With that in mind, I'd fetch values beforehand and map those values inside a static factory method [0]:

public class TestViewModel
{
    public static TestViewModel FromModel(TestModel model)
    {
        return new TestViewModel
        {
            Name = model.Name,
            PropertiesCommaSeparated = string.Join(", ", model.TestProperties.Select(it => it.TestProperty.Value))
        };
    }


    public string Name { get; set; }
    public string PropertiesCommaSeparated { get; set; }
}

This means you need to make sure you fetch TestProperties property from the database. Assuming TestProperties is a navigation property for a 1-to-many relationship, you would write:

var record = _dbContext.TestModel.Include(e => e.TestProperties).First();
var vm = TestViewModel.FromModel(record)
return View(vm);

One disadvantage here (still going off on the mentioned assumption) is if TestProperty entity is very large with a lot of columns, you might not want to fetch all columns from the database, but only the Value. But from the looks of it, you probably have something like this, so I wouldn't worry too much about it.

class TestProperty
{
    public string Key { get; set; }
    public string Value { get; set; }
}

[0]: I could have used a constructor here too, but I think static factory FromModel is more expressive of its intent. Both are equally valid approaches.

Thx for answer. Fetching the data first and process it afterwards is no option for me. I especially want to use it in an expression (Linq to SQL) to execute it on the database. — Joe, Jun 30 '21 at 05:41
Alright. [`string.Join` cannot be expressed with EF Core](https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions), because it implies a join + aggregation which complicates things a lot, and it's reasonable that EF team wants to leave that out. You're better off fetching the data from a view / stored procedure. But that can't be done inside an expression. This might put you in the right path https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping — abdusco, Jun 30 '21 at 05:52

Using a stored procedure in an expression

1 Answers1