edmx - Entity Framework: Where do I extend the CSDL/MSL?

Question

I'm using Entity Framework 4. I am using a database first model, meaning that I generated the EDM from the database. Now I want to add some model-defined functions. My question is ... where?

If I put them in the .edmx file, won't all my additions be clobbered the next time I update the database and generate the new EDM? I mean it says it right there at the top of the .Designer.cs file, "Manual changes to this file will be overwritten if the code is regenerated."

So, in what file do I put my additions?

Answer 1

I will take it little bit deeply because model defined functions are not very well known.

Model defined functions must be manually added to CSDL part of EDMX file. You must open file as XML and add a function. For example this model defined function is able to produce full name of the employee:

<Function Name="FullName" ReturnType="Edm.String">
  <Parameter Name="emp" Type="TestModel.Employee" />
  <DefiningExpression>
    Trim(emp.FirstName) + " " + Trim(emp.LastName)
  </DefiningExpression>
</Function>

Now you can save your EDMX and return to designer. The function will be still present but it is not visible in Model browser. You can update your model from database or delete all your entities but the function will be still defined. EF doesn't remove custom modification in CSDL part of EDMX.

Now you need to define the .NET function to be able to use this model defined function. You can do it anywhere. One way is to use partial class to context but in the same time you can just use some custom class:

public static class EdmFunctions
{
    [EdmFunction("TestModel", "FullName")]
    public static string FullName(Employee e)
    {
        throw new NotSupportedException("This function is only for L2E query.");
    }
}

And you are done. The only remaining task is using the function in Linq-to-Entities query:

using (var context = new TestEntities())
{
    var query = from e in context.Employees
                select new
                    {
                        e.Id,
                        FullName = EdmFunctions.FullName(e)
                    };
    var data = query.ToList();
    ...
}

Model defined functions are just some reusable Entity SQL which is translated to SQL so they can be only used in Linq-to-Entities queries. Model defined functions can be much more complicated.