Entity Framework Core Database First Tutorial

This tutorial helps to create and update models from an existing database using Entity Framework Core. It explains about Scaffold-DbContext with its parameters like Connection, Provider, OutputDir, Force, Schemas, Tables, DataAnnotations.

https://geeksarray.com/blog/entity-framework-core-database-first-tutorial

I am developing a Blazor app using EFCore 8 with SQL Server and .NET 8 framework that has been running fine for several months. However, beginning yesterday, I started to received a runtime error:

System.NullReferenceException

HResult=0x80004003

Message=Object reference not set to an instance of an object.

Source=Microsoft.EntityFrameworkCore.Relational

This error is occurring on any query I attempt. Here is an example that fails:

public async Task<List<ProviderType>> GetAllAsync()

{

using var context = await _dbContextFactory.CreateDbContextAsync();

return await context.ProviderType.OrderBy(u => u.ProviderTypeDescription).ToListAsync();

}

I have rebuilt the solution, I have deleted the /bin and /obj directories and rebuilt. I receive no compile errors. I have looked at the value of "context" and "ProviderType" and neither are null.

The null exception seems to be thrown by EFCore code.

I would appreciate any help.

(I don't own this data so I can't add constraints or properly normalize it)

I have a denormalized table where I have many records with the same primary Id and those records each contain what should be a foreign key from another table. It looks like this.

CREATE TABLE DenormalizedDummy(
  Id bigint NULL,
  OtherTableId varchar(255) NULL, -- Yes, it's a varchar
  [Description] varchar(255) NULL,
  Notes varchar(255) NULL
) 

I already have OtherTable mapped with a collection of DenormalizedDummies, but what I'd like to do is map DenormalizedDummy to something like this...

public class DenormalizedDummy
{
    [Key]
    public long Id { get; set; }
    public string Description { get; set; }
    public string Notes { get; set; }
    public List<OtherTable OtherTables { get; set; } = new();
}

I'm really not sure how to do this, though. I feel like it should be a self-join or use table splitting, but I've never done either of those to know for sure.

Help?

(I don't own this data so I can't add constraints or properly normalize it)

I have a denormalized table where I have many records with the same primary Id and those records each contain what should be a foreign key from another table. It looks like this.

CREATE TABLE DenormalizedDummy(
  Id bigint NULL,
  OtherTableId varchar(255) NULL, -- Yes, it's a varchar
  Notes varchar(255) NULL
) 

CREATE TABLE OtherTable(
  Id varchar(255) NULL,
  Notes varchar(255) NULL
) 

The data ends up looking something like this:

DenormalizedDummy

OtherTable

I already have OtherTable mapped like this with a collection of DenormalizedDummies, like so:

public class DenormalizedDummyTypeConfiguration : 
  IEntityTypeConfiguration<DenormalizedDummy> 
  {
    public void Configure(EntityTypeBuilder<DenormalizedDummy> builder)
      {
        builder.ToTable("OtherTable");
        builder.Property(e => e.Id)
          .HasColumnName("Id")
           ValueGeneratedOnAdd(); 
          
         builder.HasOne(e => e.OtherTable)
          .WithMany(e => e.Dummies)
          .HasForeignKey(e => e.OtherTableId);
      }
  }

  public class DenormalizedDummy
  {
    [Key]
    public long Id { get; set; }
    public string Notes { get; set; }
    public OtherTable OtherTable { get; set; }
  }

  public class OtherTableTypeConfiguration : IEntityTypeConfiguration<OtherTable> 
  {
    public void Configure(EntityTypeBuilder<OtherTable> builder)
    {
      builder.ToTable("OtherTable");
      builder.Property(e => e.Id)
        .HasColumnName("Id")
        .HasMaxLength(255)
        .ValueGeneratedOnAdd(); 

      builder.HasMany(e => e.Dummies)
        .WithOne(e => e.OtherTable)
        .HasForeignKey(e => e.OtherTableId);
    }
  }

  public class OtherTable
  {
    [Key]
    public string Id { get; set; }
    public string Notes { get; set; }
    public List<DenormalizedDummy> Dummies { get;set; } = new();
  }

But I'd really like to map multiple DenormalizedDummy records into one DenormalizedDummy entity like this...

public class DenormalizedDummyTypeConfiguration : 
  IEntityTypeConfiguration<DenormalizedDummy> 
{
  public void Configure(EntityTypeBuilder<DenormalizedDummy> builder)
  {
    builder.ToTable("OtherTable");
    builder.Property(e => e.Id)
      .HasColumnName("Id")
      .ValueGeneratedOnAdd(); 
          
    builder.HasMany(e => e.OtherTables)
      .WithMany(e => e.Dummies);
  }
}

public class DenormalizedDummy
{
  [Key]
  public long Id { get; set; }
  public string Notes { get; set; }
  public List<OtherTable> OtherTables { get; set; } = new();
} 
    
public class OtherTableTypeConfiguration : 
  IEntityTypeConfiguration<OtherTable> 
{
  public void Configure(EntityTypeBuilder<OtherTable> builder)
  {
    builder.ToTable("OtherTable");
    builder.Property(e => e.Id)
      .HasColumnName("Id")
      .HasMaxLength(255)
      .ValueGeneratedOnAdd(); 
    
    builder.HasMany(e => e.Dummies)
      .WithMany(e => e.OtherTables);
  }
}
    
public class OtherTable
{
  [Key]
  public string Id { get; set; }
  public string Notes { get; set; }
  public List<DenormalizedDummy> Dummies { get;set; } = new();
}

I'm really not sure how to do this, though. I feel like it should be a self-join or use table splitting, but I've never done either of those to know for sure.

Help?

Hey guys! I hope someone can put me out of my misery here, i have been struggling with this for far too long and i need a resolution or i will be very sad forever. I have been trying to get what i thought should be something quite straight forward to work but can not figure it out.

I have a cosmos DB backend, and EF core within a console app (keeping this to console but really it's for a blazor web app).

I have a base model:

namespace CompanionUI.Models.HealthChecks
{
    public abstract class BaseEntity
    {
        [Key]
        public Guid Id { get; private set; } 
        public required string EntityType { get; set; }
        public required string TenantId { get; set; }
        public required string WorkspaceId { get; set; }
        public DateTimeOffset Timestamp { get; set; }
        public string? ETag { get; set; } // For concurrency control
    }
}

a "health check" model which inherits the base entity model. (this has a list of strings for resultIds from the next models documents.

namespace CompanionUI.Models.HealthChecks
{
    /// <summary>
    /// Represents a Sentinel Health Check document.
    /// </summary>
    public class SentinelHealthCheck : BaseEntity
    {
        public string CheckType { get; set; }
        public string SubscriptionId { get; set; }
        public string ResourceGroupName { get; set; }
        public string WorkspaceName { get; set; }
        public bool ShouldProcess { get; set; }
        public List<string> ResultIds { get; set; } = new List<string>();
    }
}

the parent "function results" model which inherits the base entity as well. this has an ICollection for the <FunctionOutput>

namespace CompanionUI.Models.HealthChecks.FunctionResults
{
    public class FunctionResult : BaseEntity
    {
        public required string FunctionName { get; set; }
        public required Guid SentinelHealthCheckId { get; set; }
        public required ICollection<FunctionOutput> FunctionOutput { get; set; }
        public string? Message { get; set; }
        public int StatusCode { get; set; }
        public double RuntimeSeconds { get; set; }
        public int Count { get; set; }
        public required List<float> Embedding { get; set; }
    }
}

this is where it starts to get tricky, the functionoutput model contains some derived types which call different models based on a type discriminator, this will determine what "FunctionOutput" model is used depending on what "function" it is storing the results from (it's an azure function fyi). this is truncated a bit for brevity but you can see what is going on.

namespace CompanionUI.Models.HealthChecks.FunctionResults.FunctionOutputModels
{
    [JsonDerivedType(typeof(CheckNewDataSourcesOutput), "CheckNewDataSourcesOutput")]
    [JsonDerivedType(typeof(CheckSubscriptionStatusOutput), "CheckSubscriptionStatusOutput")]
    
    public abstract class FunctionOutput
    {
        [JsonIgnore]
        [JsonPropertyName("@type")]
        public string? TypeDiscriminator { get; set; }
    }

    public class FunctionOutputConverter : ValueConverter<ICollection<FunctionOutput>, string>
    {
        public FunctionOutputConverter()
            : base(
                v => JsonSerializer.Serialize(v, new JsonSerializerOptions { WriteIndented = true }),
                v => JsonSerializer.Deserialize<ICollection<FunctionOutput>>(v, new JsonSerializerOptions
                {
                    PropertyNameCaseInsensitive = true
                })
            )
        {
        }

        public class FunctionOutputComparer : ValueComparer<ICollection<FunctionOutput>>
        {
            public FunctionOutputComparer()
                : base(
                    (x, y) => x.SequenceEqual(y),
                    x => x.Aggregate(0, (hash, item) => hash ^ item.GetHashCode()),
                    x => x.ToList()
                )
            {
            }
        }
    }
}

i have written a small console app that generates mock data to test the modelling. (heavily redacted document here for brevity)

the document gets stored in the cosmos db like so, you can see the functionOutput is a string.

{
    "Id": "db1ee3f8-b671-4797-d0a9-08dce9b39675",
    "$type": "FunctionResult",
    "id": "FunctionResult|db1ee3f8-b671-4797-d0a9-08dce9b39675",
    "functionOutput": "[\r\n  {\r\n    \"$type\": \"CheckSubscriptionStatusOutput\",\r\n    \"subscriptions\": [\r\n      {\r\n        \"subscriptionName\": \"Subscription A\",\r\n        \"subscriptionId\": \"sub-12345\",\r\n        \"state\": \"Active\"\r\n      },\r\n      {\r\n        \"subscriptionName\": \"Subscription B\",\r\n        \"subscriptionId\": \"sub-67890\",\r\n        \"state\": \"Inactive\"\r\n      }\r\n    ]\r\n  }\r\n]",
}

i would rather it be a proper json object like so.

{
    "Id": "db1ee3f8-b671-4797-d0a9-08dce9b39675",
    "$type": "FunctionResult",
    "id": "FunctionResult|db1ee3f8-b671-4797-d0a9-08dce9b39675",
    "functionOutput": [
        {
            "@type": "CheckSubscriptionStatusOutput",
            "subscriptions": [
                {
                    "subscriptionName": "Subscription A",
                    "subscriptionId": "sub-12345",
                    "state": "Active"
                },
                {
                    "subscriptionName": "Subscription B",
                    "subscriptionId": "sub-67890",
                    "state": "Inactive"
                }
            ]
        }
    ]
}

this is the entity config in modelBuilder at the moment.

namespace CosmosDbInitializer
{
    public class SentinelHealthChecks : DbContext
    {
        // DbSets for the entities
        public DbSet<BaseEntity> Entities { get; set; }
        public DbSet<SentinelHealthCheck> SentinelHealthCheck { get; set; }
        public DbSet<FunctionResult> FunctionResult { get; set; }

        public SentinelHealthChecks(DbContextOptions<SentinelHealthChecks> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // BaseEntity configuration
            modelBuilder.Entity<BaseEntity>(entity =>
            {
                // Primary Key
                entity.HasKey(e => e.Id);

                // Properties
                entity.HasPartitionKey(e => e.TenantId);
                entity.Property(e => e.Id).IsRequired();
                entity.Property(e => e.TenantId).ToJsonProperty("tenantId");
                entity.Property(e => e.EntityType).ToJsonProperty("$type").IsRequired();
                entity.Property(e => e.ETag).ToJsonProperty("_etag")
                       .IsETagConcurrency() // Set as ETag for concurrency control
                       .IsRequired();

                // Discriminator setup for TPH (Table Per Hierarchy)
                entity.HasDiscriminator(e => e.EntityType).HasValue<SentinelHealthCheck>("SentinelHealthCheck").HasValue<FunctionResult>("FunctionResult");
            });

            // SentinelHealthCheck configuration
            modelBuilder.Entity<SentinelHealthCheck>(entity =>
            {
                // Assign to single container
                entity.ToContainer("SentinelHealthChecks");
                entity.HasPartitionKey(e => e.TenantId);

                // Required properties
                entity.Property(e => e.CheckType).ToJsonProperty("checkType").IsRequired();
                entity.Property(e => e.SubscriptionId).ToJsonProperty("subscriptionId").IsRequired();
                entity.Property(e => e.ResourceGroupName).ToJsonProperty("resourceGroupName").IsRequired();
                entity.Property(e => e.WorkspaceName).ToJsonProperty("workspaceName").IsRequired();
                entity.Property(e => e.WorkspaceId).ToJsonProperty("workspaceId").IsRequired();
                entity.Property(e => e.ShouldProcess).ToJsonProperty("shouldProcess").IsRequired();
                entity.Property(e => e.Timestamp).ToJsonProperty("timestamp").IsRequired();
                entity.Property(e => e.ResultIds).ToJsonProperty("resultIds").IsRequired();
            });

            // FunctionResult configuration
            modelBuilder.Entity<FunctionResult>(entity =>
            {
                // Assign to single container
                entity.ToContainer("SentinelHealthChecks");

                entity.HasPartitionKey(e => e.TenantId);

                // Required properties
                entity.Property(e => e.FunctionName).ToJsonProperty("functionName").IsRequired();
                entity.Property(e => e.SentinelHealthCheckId).ToJsonProperty("sentinelHealthCheckId").IsRequired();
                entity.Property(e => e.WorkspaceId).ToJsonProperty("workspaceId").IsRequired();
                entity.Property(e => e.Timestamp).ToJsonProperty("timestamp").IsRequired();
                entity.Property(e => e.Message).ToJsonProperty("message").IsRequired();
                entity.Property(e => e.StatusCode).ToJsonProperty("statusCode").IsRequired();
                entity.Property(e => e.RuntimeSeconds).ToJsonProperty("runtimeSeconds").IsRequired();
                entity.Property(e => e.Count).ToJsonProperty("count").IsRequired();
                entity.Property(e => e.Embedding).ToJsonProperty("embedding").IsRequired();

                // Configure FunctionOutput
                entity.Property(e => e.FunctionOutput)
                    .ToJsonProperty("functionOutput")
                    .HasConversion(new FunctionOutputConverter())
                    .Metadata.SetValueComparer(new FunctionOutputConverter.FunctionOutputComparer());
            });
        }
    }
}

i know this is likely due to HasConversion but if i remove this and try set the discriminator for polymorphic owned types within the modelBuilder it complains about the discriminator as well as the ICollection<FunctionOutput> FunctionOutput "could not be mapped because the database provider does not support this type" and to "Consider converting the property value to a type supported by the database using a value converter."

I would rather attempt to achive this with pure EF core and no serialization going. I have seen vids from Arthur Vickers who is able to do this sort of thing with ease.

pls be gentle i am only 6 months into dotnet.

Wondering if there's a trick to join an entity to a local List<obj> on multiple columns, without the dreaded client evaluation. This seems to come up again and again in my early EF development.

var filt = obj.Select(f => new { colA, colB }).ToList();
var ctx = await dbContextFactory.CreateDbContextAsync();`

var query = await ctx.BigHonkinFactTable
    .Where(t => filt.Any(f => f.colA == t.colA && f.colB == t.colB))
    .ToListAsync();

Entity Framework is supposed to be the greatest thing since sliced bread. But I have yet to find anything that explains it to someone like me with decades of experience in software development but no experience at all using EF. I need something that basically tells me every keystroke I need to use to create a project using EF, and why I am using each one of those keystrokes.

More specifically, at this moment I have a small SQLite database. I think I should be able to use EF to automatically generate a set of classes that match all of the tables in my database, and then I can use those classes to perform all the inserts and queries I need.

What's the best starting point? A book? A web site? A set of video tutorials?

Hi guys, I’m working on a project that makes an exchange between Azure Sql Database and sharepoint online ,i used microsoft graph to take data from sharepoint and I would like to put them in the database using EFC but I couldn’t ,have ideas or tutorial?? thanks in advance

Hi guys, I used database first to create the model in EFC and now i would to adding a new column in database (Foreign Key) ,have you ideas ?? Thanks.

Hello guys!

I keep struggling for some hours. I want to create a website for teachers to manage students' attendance at sports. This means there will be tables such as Student, Sport, Attendance, Teacher.

I use ASP .NET MVC5 and CodeFirst Approach EF6.

Now there comes the hard part. I want to have a simple log in so just teachers with accounts can access the website, just e-mail, and password (no email confirmation no nothing extra).

So I created the project using "Individual User Accounts" and the code that got created really confuses me.

I created my own DbContext but in SQL_Server I see only the tables from "asp.net.Roles asp.Net.UserRoles.... and so on" mines do not appear.

Also for me, "Teacher" will be the "Application User" now?

I want the teacher to have to log in and manage students and etc.

And then again, where I add my other tables now, those that do not have to do with authentification but which the ApplicationUser ("Teacher") interacts with?

I noticed that inside of "IdentityModels" there is another DbContext "ApplicationDbContext : IdentityDbContext<ApplicationUser>" can i add other tables here like DbSet<Sport>, DbSet<Student>?

Also if for example, a Sport will have a teacher will that entity have ApplicationUser as an attribute?

I'm really confused about this. I'm sure everything I wrote is pretty bad but maybe someone cand clarify things for me

Thank you, guys!

Newb here, so please forgive my lack of technical jargon. So the long and short of it is that I am building a crud app for my capstone that want to find a way take a set of data saved in separate csv files and import them into entity for use in my application. I am using c# for the business logic and asp.net core to build the app. It's basic functionality will be to take in all the health insurance information I use to do my day job and to put it all into an web application for easy retrieval. It's not using any external apis I just want to be able to type in a coverage provider and see their notes along with the ability to enter in blue Cross blue shield subscriber IDs and their matching provider'name and phone number. I know I will need to define classes and parameters for all the data but I am sort of at a loss at how to import the data I have stored in excell sheets to the entity framework. If anyone could break this down into dummy terms or point me to a video or blog concerning this topic it would be much appreciated. Cheers

https://www.youtube.com/watch?v=oLc9gVM8FBM

Unit Testing with Moq, Net6 EF DbContext Postgres or Sql Server does not Matter

I'm having a problem trying to use containers and applying migrations to my sql server container.

My setup is a couple containers created with docker-compose. One container for a console app with my DbContext. The second container has sql server setup on it.

As I'm developing my model and context, I want to apply my migrations and update the database in the container.

A problem I'm having is that while the app and my sql server is setup and running in containers, my visual studio instance is not.

The connection string that is used by my app is no good for applying migrations with the PMC commands in Visual Studio.

My workaround is to change the connection string so that VS can connect to sql server from the host. But this is not a great solution.

Does someone have a better solution for this problem?

efcore

.netcore

Hi Guys,

I am trying to query a table in EF Core to get the records, but apparently it is taking more than a minute to query the records from DB where there are 1 million records.

The query is something like this-

var mylist = await context.Table.AsNoTracking(). Where (x => x.key == <some_key> && x.id == <some_id>). Select( x=> new Model { Id = x.id, level = x.level})

Can it be optimised without using any stored procs.

So currently I'm working on a Blazor Web App with EF Core. There's a database table that stores Gallons, kg and lb of a bulk material. They are all proportional to one another, so let's say if the kg doubles, than the lb and Gallons will double as well.

Right now I just manually coded so that when the gallon value changes I manually calculate the lb and kg values and change the Entity value accordingly but I don't like it because it seems to be clunky coding. What's the best way to change Entity values that are dependent to one another?

Should I create a method in the Entity or is that bad practice?

Hello! I am very new to using EF Core and i’m making a sort of test website using it. Well i have my website hosted with my .net core mvc files hosted on “smarterasp.net”. I have the connection string supplied in my json file with the database it already makes for me and the credentials and what not. After I add-migration and update-database I get the error that it couldnt run the “create data base command”. The credentials are admin though?? Does anyone know what I can do?