Using a stored procedure in an expression

user_3187001 0
Viewed 47

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

entity-framework-core linq-to-sql c#
1 Answers

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.

user_52981500
Related