EF Generate Database from Model Deletes Function Imports

Question

Ok I've seen this question Entity Framework: Generate Database From Model removes Stored Procedures from Model Store but I don't think this is solved. I have some function which I need to run at SQL Server in selection for filtering. I wasn't able to write that code in .NET so I just wrote that function in SQL Server as a function. I am using model-first, but I need that function at SQL level, and I use update model from database option to import the function. But when I make changes in the Entity Designer and generate database from model, the function mapping is erased. I understand that I shouldn't use model-first and db-first at the same time, but how can I solve my problem then? I've got this code that I can't write in C#, and I need it in my LINQ queries. It does work when I create an import and create the C# method with EdmFunction attribute which maps to the function at the database, but how can I make it persistent so the link between my model and my SQL function is preserved on update? Is writing the function that could translate in LINQ-to-Entities the only option?

Here is my SQL function

    create function [dbo].[HammingDistance]
    (@first bigint, @second bigint) returns int
    as
    begin
        declare @xor bigint = @first ^ @second;
        declare @one bigint = 1;
        declare @diff int = 0;
        declare @and bigint;
        while (@xor != 0)
            begin
            set @and = @xor & @one;
            if(@and = @one)
                begin
                set @diff = @diff + 1;
                end
            set @xor = @xor / 2;
            end
        return @diff;
    end

(I'm not an SQL expert to this may not be the best way of doing this, so if there's a better way, correct me)

I need this to run in database WITHOUT importing anything to ASP.NET from the DB. If you have a way to translate this code into C# that would translate to SQL code in LINQ-to-Entities, it is also welcome.

Answer 1

Did you read the answer in linked question? There is nothing to be solved. It is simply not supported by the designer. Database first and model first approaches are exclusive. You can use either one the second but combining them is not supposed workflow of using the designer.

What you can do? You can write your own database generation workflow for that - you will find the default workflow here:

%VSINSTALLDIR%\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\TablePerTypeStrategy.xaml

You need to replace the first activity with your own which will put your function import into SSDL after generation. You can also try to use Database generation power pack which offers more functionality for database generation and among these functionalities it has T4 templates for SSDL generation where hardcoding function import should be much easier then creating workflow activity.

For last you can spend some money and buy a tool which will allow you to do much more magic then standard designer offer you. You can try trial of Huagati EDMX tools and its model comparer.

Btw. why are you using model first? Model first and code first are for scenarios where you don't bother with crappy database generated for you and where the most complicated logic in the database are auto generated id.

In my opinion model first or code first is not for a real application where you expect anything more then dumb data persistence with small amount of data, small traffic and absolutely no logic in the database. Yes, your scenario is out of scope for model first = you are using wrong workflow not expected by MS designer.