How to manipulate values in LINQ query without "Explicit construction of entity type ### in query is not allowed when trying to modify value" error

Question

Is it possible to change a field value from inside a LINQ select query for use in an ASP.NET MVC app?

For example, if a query returns a field with value "foo," I want it's output to be "bar" instead.

Essentially, I want LINQ to create this TSQL statement:

CREATE PROCEDURE GetSpecialCaseOfMyObject (
     @param varchar(10)
     ) AS

SELECT
     REPLACE( FieldA, 'foo', @param ) [FieldA]
     FieldB,
     FieldC
FROM
     MyTable
WHERE
     FieldA = @param

I've gotten this far, and it builds, but...

public IQueryable<MyObject> GetSpecialCaseOfMyObject (string myParam)
{
        var result = from o in Context.MyObject
                     where o.FieldA = myParam
                     select new MyObject
                        {
                                FieldA = o.FieldA.Replace("foo", myParam),
                                FieldB = o.FieldB,
                                FieldC = o.FieldC
                        };

        return result;
}

... when I try run a View to return data from it, I get a yellow screen of death:

Explicit construction of entity type 'Model.DataAccess.MyObject' in query is not allowed.

I tried using an anonymous type, but then I can't cast it back to MyObject.

Is what I want to do possible without jumping through all kinds of hoops?

Answer 1

I wouldn't bother modifying the query logic. It is the responsibility of the view to format the output. So leave the query as is:

public IQueryable<MyObject> GetMyObject (string myParam)
{
    return from o in Context.MyObject
           where o.FieldA = myParam
           select o;
}

and in your view format as necessary:

<div><%= Html.Encode(Model.FieldA.Replace("foo", "bar")) %></div>

or even better write a helper:

public static string FormatFieldA(this MyObject myObject)
{
    return myObject.FieldA.Replace("foo", "bar");
}

which could be used as:

<div><%= Html.Encode(Model.FormatFieldA()) %></div>

Answer 2

This would be really easy if you had a domain model or even a view model. You can use a tool like AutoMapper to handle most of the grunt work. All you have to do is create a class that's similar to the entity model:

public class MyViewObject
{
    public MyViewObject(string fieldA, string fieldB, string fieldC)
    {
        this.FieldA = fieldA;
        this.FieldB = fieldB;
        this.FieldC = fieldC;
    }

    public string FieldA { get; set; }
    public string FieldB { get; set; }
    public string FieldC { get; set; }
}

And then map it (I'm showing a manual mapping here but as I said, you can automate a lot of this with a an automapping tool):

public IEnumerable<MyViewObject> GetViewObjects(string arg)
{
    var dataObjects = GetMyObjects(arg);
    return dataObjects.Select(o => TransformMyObject(o, "bar"));
}

public MyViewObject TransformMyObject(MyObject o, string fooReplacement)
{
    return new MyViewObject(o.FieldA, o.FieldB,
        o.FieldC.Replace("foo", fooReplacement);
}

This really isn't that much work and it will make it a lot easier to support future changes to the presentation logic. It's also OK to have some of this logic in the view, if it's really simple formatting, but I prefer to keep views as dumb as possible, with View Models containing mostly (or entirely) string properties.

It may seem like "jumping through hoops" now, but once you have a separate domain and/or view model you will wonder how you ever got by without one. When your app is missing an entire layer, you start to run into these odd leaky-abstraction issues.