Entity Framework Core define property using a UDF

Sami.C 0
Viewed 34

New to EF and have the following scenario. I've created a couple of very basic tables/classes to demonstrate what I want to achieve (excuse any potential spelling issues that may be present below, I've just typed this out as an example).

   //Model Class
    public class Customer{
        public long Id{ get; private set; }
        public string name{ get; private set; }
        public long status{ get; private set; }
    }

//configuration
public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customer");

        builder.HasKey(e => e.Id);

        builder.Property(e => e.Id)
            .HasColumnName("Id")
            .ValueGeneratedNever()
            .IsRequired();

        builder.Property(e => e.name)
            .HasColumnName("name")
            .IsRequired();

    }
}

//Tables
CREATE TABLE [dbo].[Customer](
    [Id] [uniqueidentifier] NOT NULL,
    [name] [nvarchar](255) NOT NULL )


CREATE TABLE [dbo].[CustomerJournal](
    [Id] [bigint] NOT NULL,
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [bigint] NOT NULL,
    [StatusId] [bigint] NOT NULL,
    [StatusAlias] [varchar](32) NOT NULL,
    [StatusName] [nvarchar](128) NOT NULL,
    [CreateDate] [datetimeoffset](7) NOT NULL
)


ALTER TABLE [dbo].[CustomerJournal]  WITH CHECK ADD  CONSTRAINT [FK_CustomerJournal_Customer] FOREIGN KEY(CustomerId)
REFERENCES [dbo].[Customer] ([Id])
GO

CustomerJournal is a journal of statuses for a customer. I do not have a StatusId on the Customer table, rather I would like to call a UDF to retrieve the latest journal entry.

CREATE Function [dbo].[CustomerStatus](
   @CustomerId BIGINT
)
RETURNS BIGINT
BEGIN
    DECLARE @StatusID BIGINT

    SELECT  
        TOP 1
        @StatusID = [StatusId]
    FROM
        [dbo].[CustomerJournal] WITH (NOLOCK)
    WHERE
        [CustomerId] = @CustomerID
    AND
    ORDER BY
        [RowID] DESC
    
    RETURN @StatusID

END

How could I add a property "status" to my configuration class which passes in my Id into this UDF and retrieves it??

Thanks

.net-core entity-framework-core entity-framework c#
1 Answers

You can Add a Calculated Column to your Table and use that:

public class Customer{
    public long Id{ get; private set; }
    public string name{ get; private set; }
    public long status{ get; private set; }
    // Do this if you are using a DB-First approach
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public long LastJournalEntryId {get;set;}
}

//configuration
public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customer");

        builder.HasKey(e => e.Id);

        builder.Property(e => e.Id)
            .HasColumnName("Id")
            .ValueGeneratedNever()
            .IsRequired();

        builder.Property(e => e.name)
            .HasColumnName("name")
            .IsRequired();

        // Do this if you are using a Code-First approach
        // "GetLastJournalEntryIdForCustomer" is the name of your UDF
        builder.Property(e => e.LastJournalEntryId)
            .HasComputedColumnSql("GetLastJournalEntryIdForCustomer(Id)");
    }
}

Depending on whether you're using Code-First or DB-First you need to create it in the appropriate place first of course.

If you want to map the last Journal Entry to the customer using that column it should work like this:

public class Customer{
    public long Id{ get; private set; }
    public string name{ get; private set; }
    public long status{ get; private set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [ForeignKey(nameof(LastJournalEntry))]
    public long LastJournalEntryId {get;set;}
    public CustomerJournal LastJournalEntry {get;set;}
}
public class CustomerJournal
{
    public long Id {get; set;}
    public Customer Customer {get;set;}
    public String StatusName {get;set;}
}
jeanluc1620
Related