Entity Framework 6 Code First Custom Functions
You should be able to use a scalar SQL function in your Where
criterias with CodeFirstStoreFunctions
Assuming you want to map SQL function [dbo].[LatLongDistanceCalc], and according to the test suite:
public class MyDataContext: DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//...
modelBuilder.Conventions.Add(new FunctionsConvention("dbo", this.GetType()));
}
// "CodeFirstDatabaseSchema" is a convention mandatory schema name
// "LatLongDistanceCalc" is the name of your function
[DbFunction("CodeFirstDatabaseSchema", "LatLongDistanceCalc")]
public static int LatLongDistanceCalc(int fromLat, int fromLong,
int toLat, int toLong)
{
// no need to provide an implementation
throw new NotSupportedException();
}
}
usage would then be:
context.Locations
.Where(e => MyDataContext.LatLongDistanceCalc(e.Lat, e.Long, lat, long) >= 10)
Using EF6 Store Functions for Entity Framework code-first, can I return a custom type?
The type to be returned must have columns named the same as the function. For example, if the function returns columns:
Name nvarchar
Sum int
then SomeClassSummary should be:
public class SomeClassSummary {
public string Name { get; set; }
public int Sum { get; set; }
}
Then in the context, add the class as a complex type:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.ComplexType<SomeClassSummary>();
modelBuilder.Conventions.Add(new FunctionsConvention<MyContext>("dbo"));
}
Entity Framework Code First Function
This may provide an answer to what you need.
Code first Entity Framework 6.1 Custom Aggregate Function
As @srutzky alluded to in the comments, EF doesnt seem to like binding to aggregate functions with multiple parameters. So you have to change percentile function to a median function or whatever fixed percentile you are interested (you will need to update your SqlClr function so the parameters match as well)
public class MySqlFunctions
{
[DbFunction("dbo", "Median")]
public static float? Median(IEnumerable<float?> arg)
{
throw new NotSupportedException("Direct calls are not supported.");
}
}
The next step is letting EF know that a the database has a function called median We can do this in our DbContext. Create a new convention to access the the dbModel then we add the function in the dbModel. You must make sure the parameters and the parameter types match both the SQL and the C# function exactly.
public class EmContext : DbContext
{
...
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
//Register a convention so we can load our function
modelBuilder.Conventions.Add(new AddMedianFunction());
...
}
public class AddMedianFunction : IConvention, IStoreModelConvention<EntityContainer>
{
public void Apply(EntityContainer item, DbModel dbModel)
{
//these parameter types need to match both the database method and the C# method for EF to link
var edmFloatType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Single);
//CollectionType constructor is internal making it impossible to get a collection type.
//We resort to reflection instantiation.
var edmFloatListType = CreateInstance<CollectionType>(edmFloatType);
var medianfunction = EdmFunction.Create("Median", "dbo", DataSpace.SSpace, new EdmFunctionPayload
{
ParameterTypeSemantics = ParameterTypeSemantics.AllowImplicitConversion,
IsComposable = true,
IsAggregate = true,
Schema = "dbo",
ReturnParameters = new[]
{
FunctionParameter.Create("ReturnType", edmFloatType, ParameterMode.ReturnValue)
},
Parameters = new[]
{
FunctionParameter.Create("input", edmFloatListType, ParameterMode.In),
}
}, null);
dbModel.StoreModel.AddItem(medianfunction);
dbModel.Compile();
}
public static T CreateInstance<T>(params object[] args)
{
var type = typeof(T);
var instance = type.Assembly.CreateInstance(
type.FullName, false,
BindingFlags.Instance | BindingFlags.NonPublic,
null, args, null, null);
return (T)instance;
}
}
}
With all that in place you should just be able to call your function as expected
paymentsTable
.GroupBy(x=>x.CustomerId)
.Select(new{
Median = MySqlFunctions.Median(x.Select(g=>g.Amount))
});
Note: I am already assume you have loaded your SqlClr function which I have not covered here
Related Topics
I Want to Understand the Lambda Expression in @Html.Displayfor(Modelitem => Item.Firstname)
How to Copy a File While It Is Being Used by Another Process
Nested "From" Linq Query Expressed with Extension Methods
Autonumber with Entity Framework
C# - Set Directory Permissions for All Users in Windows 7
Using Mediaelement to Play Video from Stream
Invoking Methods with Optional Parameters Through Reflection
File.Copy VS. Manual Filestream.Write for Copying File
Click Through Transparency for Visual C# Window Forms
Entity Framework - Retrieve Id Before 'Savechanges' Inside a Transaction
Blocking Dialogs in .Net Webbrowser Control
How to Declare One to One Relationship Using Entity Framework 4 Code First (Poco)
Two Dimensional Array Slice in C#
Is There Any Valid Reason to Ever Ignore a Caught Exception
Struggling Trying to Get Cookie Out of Response with Httpclient in .Net 4.5