0

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?

Improve this question
4
  • Just for ensure, Why you add such function if you can create LINQ equivalent of the same function. Commented Jul 5, 2021 at 13:05
  • You could use a similar approach as a recommended approached for stored procs: stackoverflow.com/questions/20715292/… Commented Jul 5, 2021 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. Commented Jul 6, 2021 at 3:45
  • 1
    @MustafaTaeb, if you create IQuryable methothod with custom projection, you will return only needed data. Your SP can be easily replaced by LINQ. Commented Jul 6, 2021 at 5:43

2 Answers 2

Reset to default
1

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
            });
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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>();
                });
    }
Improve this answer

4 Comments

How to avoid multiple time execution of this command ?
@MustafaTaeb you can use IF NOT EXISTS in your script to avoid multiple executions.
Hmm, and where is EF Core code-first in this "answer"? The correct answer is here stackoverflow.com/a/51998117/5202563
@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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.