pátek 21. března 2014

EF: intColumn.ToString() doesn't work in LINQ to Entities, SqlFunctions.StringConvert outside


As you may already know LINQ to Entities will not let you do something like
  "Blah blah blah " + t.charColumn + " (" + t.intColumn.ToString() + ")"
in a query. What quite a few do not know is that you can instead use 
  "Blah blah blah " + t.charColumn + " (" + System.Data.Objects.SqlClient.SqlFunctions.StringConvert(t.intColumn) + ")"

there is a catch though ... this will ONLY work inside a query! So if you happen to have an entity already, you can't use this ... it would probably be too hard to implement, much harder than throwing an exception ...

Why do I care? Generated code. Suppose you have quite a few lookup tables (countries, types of this, types of that, regions, ... anything really) and you need to populate the pulldowns. Sometimes it's enough to display one property, sometimes you want to do something a bit more complicated ... like include the ID in the displayed text. To simplify things for the developers you extend the EF designer so that they could specify that an entity may be turned into a dropdown and what's the property/expression to display. And the tweaked .tt file generates the interface implementation and the extension methods that turn an IQueryable of entities to an IEnumerable<SelectListItems> etc. etc. etc. With the extension methods written so that we do not fetch the whole entities, but rather Select() just what we need. 
And here comes the catch of course. The same code to compute the text to display is used in
 query.Select(e => new {Value = e.Id, Text = HERE}).AsEnumerable().Select(s => new SelectListItem{Id = s.Id, Text = s.Text})
and in
 string IListItem.Text { { get { return HERE } }
where the later is used when I already have an entity and need to display the text as in the drop down.
And seems if I need to include the ID in the text there is no solution that'd work both in LINQ to Entities and outside.
Or am I wrong?

Yes, I am wrong. There is a solution. The same one I used to allow me to use LIKE in LINQ to Entities queries. 

If I add this into the .edmx into <edmx:Runtime><edmx:ConceptualModels><Schema> :
<Function Name="Int32_SqlToString" ReturnType="Edm.String" ef4ex:RenameTo="SqlToString">
  <Parameter Name="val" Type="Edm.Int32" />
  <DefiningExpression>
   Cast(val as Edm.String)
  </DefiningExpression>
  <ef4ex:CodeBlock>
   return val.ToString();
  </ef4ex:CodeBlock>
 </Function>

and 
[System.Data.Objects.DataClasses.EdmFunction( "Xxxx.Models", "Int32_SqlToString")]
 public static String SqlToString(this Int32 val) {
  return val.ToString();
 }

into a static class (it's actually generated using the data from the ef4ex tags and attributes), then I can use
 intColumn.SqlToString()
anywhere.
The only problem is that in a project where I'd need two different models based on two .edmx files I'd have to define the function twice and then could only import one or the other namespace and there's be nothing preventing me from using the wrong .SqlToString() triggering a run time error.

To solve that I would have to be allowed to put several [System.Data.Objects.DataClasses.EdmFunction()] attributes to one extension method and the LINQ to Entities would have to select the right EntitySQL implementation based on the namespaceName.
It's a shame there is (as far as I know) no way to attach the EdmFunction mapping to a method written by someone else in a way the LINQ to Entities provider would understand and thus give it a hint how to convert the method into the SQL :-(

C# 6.0 - constant parameters and partial evaluation

If you work with things like ASP.Net MVC things like

Html.EditorFor(m => m.Name)

look familiar. There is a fairly big problem with this code. It works all right, but it's inefficient. The point is that it keeps on redoing the same thing again and again and again. Find out what property of what type the lambda points to, read the attributes via reflection, find the right editor template, fill in the values of those attributes ... all these things are done over and over and over again. Even though the lambda is constant, the attributes assigned to the property are constant, the templates are constant ... the only thing important for this that changes is the value of the model hidden in the HtmlHelper.

You can cache the attributes and some other stuff, but it's still not as efficient as it could be.

What would be nice would be to be able to split the contents of the EditorFor() method into the stuff that depends only on the constant parameter (the Expression) and the stuff that depends on the current value of the Model and ViewState. To make things easier let's make a much simpler example. Suppose we have a method like this:

public static float Foo(float x, float y) {
  return Math.sqrt(x) + Math.Sqrt(y);
}


Now, Math.Sqrt() is not a particularly expensive method, but suppose whatever we do with the parameter x is expensive and we have lots of calls like this

var result = Our.Foo( 5.7, y)

so we would like to do the computation that only depends on x once. We we would add one more overload of the Foo() method like this:

public 
static float Foo(const float x, float y) {
  var sqrt_x = Math.Sqrt(x);
  return (y) => sqrt_x + Math.Sqrt(y);
}


which would get compiled as

public Func<float,float> Foo_const_x(float x) {
  var sqrt_x = Math.Sqrt(x);
  return (y) => sqrt_x + Math.Sqrt(y);
}


And then the call above would get compiled as

if (_generated_static_for_const_163 == null)

   _generated_static_for_const_163 = Foo_const_x(5.7);
var result = 
_generated_static_for_const_163(y)
...
private static Func<float,float>
_generated_static_for_const_163;


Caching in current C#

I know that in some cases caching inside the method would work:

private static ConcurrentDictionary<float,float> _cacheFooX = new ConcurrentDictionary
<float,float>();
public static float Foo(float x, float y) {
  var sqrt_x = _cacheFooX.GetOrAdd(x, xx => Math.Sqrt(xx));
  return sqrt_x + Math.Sqrt(y);
}


but there are several problems with this solution.

  1. It's kinda wordy. 
  2. Not all objects may be used as a hash key either at all or with reasonable performance. 
  3. This way you cache all values of "x" the method is ever called with, not just the constants. Sometimes it's OK, sometimes it'd cause huge memory footprint with no speed gain. It makes sense to cache Our.Foo( 4.7, y), especially if it's inside an often called method. It doesn't make sense to cache for (var x = 0; x < 100; x += 0.002) { var r = Our.Foo(x, 78); ...
  4. If the thing you'd like to do just once is not to compute some value, but rather to decide which of the twenty possible actions you want to do with the rest of the parameters, the caching doesn't work so well.
An example of the thing that would work well with the const proposal and not so well with the cache could go something like this:

public static MvcHtmlString Input<TModel,TValue>(this HtmlHelper<TModel> html, Expression<Func<TModel,TValue>expr) {
  var info = new InfoAboutProperty(expr); // lots of use of reflection etc.
  if (info.AllowsMultipleValues) {
   if (info.RenderAs == RenderAs.Multiselect) {
    do whatever it takes to render the select multiple, get the options based on the attributes, etc.
    return theGeneratedHtml;
   } else if (info.RenderAs == RenderAs.Checkboxes) {

    do whatever it takes to render the intput type="checkbox"es, get the options based on the attributes, etc.
    return theGeneratedHtml;
   } else ...

With the const it's simply

public static MvcHtmlStrig Input<TModel,TValue>(this HtmlHelper<TModel> html, const Expression<Func<TModel,TValue>> expr) {
  var info = new InfoAboutProperty(expr); // lots of use of reflection etc.
  if (info.AllowsMultipleValues) {
   if (info.RenderAs == RenderAs.Multiselect) {
    return html => {
     do whatever it takes to render the select multiple, get the options based on the attributes, etc.
     return theGeneratedHtml;
    };
   } else if (info.RenderAs == RenderAs.Checkboxes) {
    return html => {
     do whatever it takes to render the intput type="checkbox"es, get the options based on the attributes, etc.
     return theGeneratedHtml;
    };

   } else ...

Generic methods

In the case of generic methods sometimes a lot of work can be done just knowing the type so it would be great if the const modifier could be applied even to the type parameters:

public static something Foo<const T>(T x) {
  do some reflection or something using the type T
  return x => { ...};
}

Implementation could be exactly the same.

Of course for this to really be of use, expressions and lambdas with no enclosed variable would have to be considered constants :-)