Create SQL Server function using code-first approach in ASP.NET Core at migration time

Question

I have database function like this :

CREATE FUNCTION [dbo].[FuelByOrganization] 
    (@organziationId INT = 0)
RETURNS TABLE
AS 
    RETURN 
        SELECT 
            dbo.FuelDelivery.ID AS FuelDeleveryId, 
            dbo.Contracts.ContractNumber AS ContractLetterNumber, 
        FROM  
            dbo.FuelDelivery 
        INNER JOIN  
            dbo.Contracts ON dbo.FuelDelivery.ContractID = dbo.Contracts.ID
        WHERE 
            dbo.Contracts.OrganizationId = @organziationId;

I want to run this command while I am migrating my database. I am using a code-first approach, how can I create this function during my migrations?

Just for ensure, Why you add such function if you can create LINQ equivalent of the same function. — Svyatoslav Danyliv, Jul 05 '21 at 13:05
You could use a similar approach as a recommended approached for stored procs: https://stackoverflow.com/questions/20715292/create-stored-procedures-using-entity-framework-code-first — Ben Osborne, Jul 05 '21 at 13:54
@SvyatoslavDanyliv because I want to do that in database side for performance issue .I do not want to return all the data I want some specific one. — Mustafa Taeb, Jul 06 '21 at 03:45
@MustafaTaeb, if you create IQuryable methothod with custom projection, you will return only needed data. Your SP can be easily replaced by LINQ. — Svyatoslav Danyliv, Jul 06 '21 at 05:43

score 1 · Answer 1 · answered Jul 06 '21 at 05:57

Alternative answer: If you plan you use such function in LINQ queries - do not create such simple Stored Procedures, but replace them with IQueryable methods. Then you can combine IQueryable with other LINQ queries.

public static class BusinessFunctions
{
    public static IQueryable<FuelByOrganizationResult> FuelByOrganization(this MyContext ctx, int? organziationId = default)
    {
        var query = 
            from fd in ctx.FuelDelivery
            from c in fd.Contracts
            select new {
                fd,
                c
            };

        if (organziationId != bull)
            query = query.Where(q => q.c.OrganizationId = organziationId);

        return query.Select(q =>
            new FuelByOrganizationResult
            {
                FuelDeleveryId = q.fd.Id,
                ContractNumber = q.c.ContractNumber
            });
    }
}

score 0 · Accepted Answer · answered Jul 06 '21 at 05:33

0

You can run your script in the main function of your program.

 public class Program
    {
        public static void Main(string[] args)
        {
            var host = CreateHostBuilder(args).Build();
            // Run your script here
            host.Run();          
        }
        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
    }

answered Jul 06 '21 at 05:33

Mohammad Zubair Akrami

54
6

How to avoid multiple time execution of this command ? – Mustafa Taeb Jul 06 '21 at 05:39
@MustafaTaeb you can use IF NOT EXISTS in your script to avoid multiple executions. – Mohammad Zubair Akrami Jul 06 '21 at 06:02
Hmm, and where is EF Core code-first in this "answer"? The correct answer is here https://stackoverflow.com/a/51998117/5202563 – Ivan Stoev Jul 06 '21 at 07:37
@IvanStoev ok, but whenever you run the `add-migration command, then you have to go to the migration generated file, and put the mentioned code in up function that is a little bit messy and troublesome. – Mohammad Zubair Akrami Jul 06 '21 at 12:04

Create SQL Server function using code-first approach in ASP.NET Core at migration time

2 Answers2