Custom CRUD stored procedures using code first

Question

0.00/5 (No votes)

See more:

I've been using EF6 code-first approach on a new project, and it has been wonderful, but now I struggling a bit on how to customized the CRUD stored procedures, so that some columns are computed in the database.

Process (abstract):

public abstract class Process
{
    public int Id {get; set}
    public int Name {get; set}
    public DateTime CreateDate{get; private set}
    public DateTime UpdateDate{get; private set}

    public virtual ICollection<ProcessContact> Contacts { get; set; }
}

ProcessContact:

public class ProcessContact
{
    public string Name {get; set}
    public string Value {get; set}
}

ProcessA:

public class ProcessA: Process
{
    public int A {get; set}
}

ProcessB:

public class ProcessB: Process
{
    public int B {get; set}
}

Given the 4 classes above as an example how can I in EF6 code-first, customized the generated stored procedure so it can for example:

-> On insert of Process, set the Id as count(*)+1 and CreateDate = GETUTCTIME()
-> On update of ProcessContact, set UpdateDate = GETUTCTIME() in the Process table

Thanks

What I have tried:

I´ve been looking at migrations, but to be honest I´m struggling a bit with it.

Posted 11-Aug-18 8:19am

Member 13315405

Updated 11-Aug-18 9:04am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2018-08-11T09:05:00

First, EF does not generate stored procedures. Everything is done through normal SQL statements.

Next, your idea of "count(*) + 1" to generate a record ID value is ridiculous and degrades in performance over time as the number of records increases. Record ID values should be database generated, not programmed via formula. This is quite easily done in EF as it's just an attribute to add to the field you're using, like:

C#

public abstract class Process
{
    [Key]
    public int Id {get; set}
    public int Name {get; set}
    public DateTime CreateDate{get; private set}
    public DateTime UpdateDate{get; private set}

    public virtual ICollection<ProcessContact> Contacts { get; set; }
}

or by convention. EF assumes the key is specified by the class name with "Id" appended to it:

C#

public abstract class Process
{
    public int ProcessId {get; set}
    public int Name {get; set}
    public DateTime CreateDate{get; private set}
    public DateTime UpdateDate{get; private set}

    public virtual ICollection<ProcessContact> Contacts { get; set; }
}

The Update datetime field can be done in a few different ways. EF6 can use stored procedures to perform database operations. YOU have to write the stored procedures, bake them into the database, and then tell EF how to use it.

Or, you can just set the field in your C# code and send the update to the database using normal SaveChanges in your EF context.