Formatting Select Statement Using Dynamic Linq

Question

I've been looking into this for quite some time now and cannot figure out a resolution. I Originally Tried formatting A dynamic linq Statement as you can see here in this post

I declared a class:

public class DynamicHelper
{
    public string FormattedLink(string DisplayText, string ID)
    {
        return "<a href='/Title/Preview/'" + ID + ">" + DisplayText + "</a>";
    }

    public string FormattedLink(string DisplayText, int ID)
    {
        return "<a href='/Title/Preview/'" + ID + ">" + DisplayText + "</a>";
    }
}

After I inserted a new type in DynamicLinq into the predefinedTypes

,typeof(DynamicHelper) //around line 635

I have a program which is attempting to Invoke the FormattedLink inside of a dynamic linq select:

using (var Model = new MK3Entities())
{
    DynamicHelper Dh = new DynamicHelper();

    var TOrigin = (Model.Titles.Where("ID > 19632")
                               .Select("new(ID,  @0.FormattedLink(ExtTitleID, ID) as ExtTitleID )", Dh) as System.Collections.IEnumerable)
                               .Cast<dynamic>().Take(10).ToList();

    Console.ReadKey();
}

When I execute this program I get a runtime exception "LINQ to Entities does not recognize the method 'System.String FormattedLink(System.String, Int32)' method, and this method cannot be translated into a store expression."

Any Ideas on how to fix this... I just need simple formatting from Dynamic Select.

score 1 · Accepted Answer · answered Oct 22 '14 at 20:26

1

The error message is pretty self explanatory. The database doesn't know how to translate that method into SQL. You need to fetch the information that the method needs in your database query and then call that function on the results, rather than in the query.

answered Oct 22 '14 at 20:26

Servy

202,030
26
332
449

That seems logical, It just didn't make sense because I expanded the library the same way so it would would work with the IList function Contains. Maybe SQL some how knows about that function or LinqToEntities can figure out a way to translate it. How do you suggest I fix it? – johnny 5 Oct 22 '14 at 20:32
1

@johnny5 You aren't going to be able to figure out a way for EF to translate it. You need to execute the function on the application side after executing your query, as I said in my answer. – Servy Oct 22 '14 at 20:37
Entity Framework does not support that function you are correct. It does Support concatenation. Perhaps you know of a way to just append some data to the results ie LinqToEntities Supports '.Select(t => new { FML = t.ExtTitleID + "EXTRATEXT" })' How can I do that dynamically please? – johnny 5 Oct 22 '14 at 21:05
@johnny5 As I've told you twice now, just execute the function on the application side. Even if you did spend the effort to do this on the DB end you wouldn't gain anything; you'd only *increase* the data needing to be sent over the wire and not improved the query in any way. Just do the work on the application side – Servy Oct 23 '14 at 14:20
I am now going to do application side – johnny 5 Oct 23 '14 at 14:38

score 0 · Answer 2 · answered Oct 22 '14 at 20:27

0

I'm not sure why you need it to be dynamic, it seems the solution you present is very overly complicated. I would write it as:

using (var Model = new MK3Entities())
{
    DynamicHelper Dh = new DynamicHelper();

    var TOrigin = Model.Titles
      .Where("ID > 19632")
      .Select(t => new { ID = t.ID, ExtTitleID = t.ExtTitleId })
      .Take(10)
      .ToList() // Execute SQL Statement
      .Select(t => new {ID = t.ID, Link = nh.FormattedLink(ExtTitleID, ID)})
      .ToList();

    Console.ReadKey();
}

I'm returning an List<anonymous'1> object instead of a dynamic object (because I've never had the need for dynamic objects) so you can adjust it accordingly.

answered Oct 22 '14 at 20:27

Erik Philips

53,428
11
128
150

This is being used to build dynamic reports, select statement must be dynamic so I can have the proper formatted columns. I have them displaying the proper columns just not formatted. If I'm doing this dynamically then after the ToList I won't be able to have early binding on the Second Select statement. Any way to solve this dynamically? – johnny 5 Oct 22 '14 at 20:35
1

Formatting columns *should not be solved by a sql statement*. Database technologies are designed to store and retrieve data. The applications that access data should format it as needed. For example, storing a DateTime in a database in a specific TimeZone is terrible idea because it's not the correct time for anyone outside that timezone. Thus it should be stored as UTC and then converted/formatted for the end user as needed. – Erik Philips Oct 22 '14 at 20:47

score 0 · Answer 3 · edited May 23 '17 at 12:20

0

I just solved similiar problem few hours back.

YOu need ToList() that works with Dynamic linq. Check out this thread: Can't find property or field on a dynamic object

Just copy paste those to your project, and later:

var TOrigin = (Model.Titles.Where("ID > 19632")
                    .ToAnonymousList()
                    .Select("new(ID,  @0.FormattedLink(ExtTitleID, ID) as
                         ExtTitleID )", Dh) as System.Collections.IEnumerable);

edited May 23 '17 at 12:20

Community

1
1

answered Oct 22 '14 at 20:44

Erti-Chris Eelmaa

25,338
6
61
78

Thanks for the response. The issue your referring to I resolved earlier the as System.Collections.IEnumerable) enables you to cast to dynamic afterwards. ToAnonymousList won't work because The Select statement afterwards can only take an Iqueryable while ToAnonymouslist returns an IList – johnny 5 Oct 22 '14 at 20:55
@johnny5, that's not necessarily true. `Select` also works with `IEnumerable`, and `IList` is `IEnumerable`. – Erti-Chris Eelmaa Oct 23 '14 at 09:31
Current Dynamic Linq Library Only Support IQueryable and IQueryable, you can extend it quite easily to support IEnumerable – johnny 5 Oct 23 '14 at 13:42

Formatting Select Statement Using Dynamic Linq

3 Answers3