Skip to main content

Fluent API EntityFramework 6 C#

Brief

We will develop simple Database Call (SQL Server) using EntityFramework using the Fluent API.

We will have below things for the DB Cal.
  1. Student Class
  2. StudentMap Class
  3. PracticeContext Class
  4. DataBaseCall Class
  5. Proram Class
  6. app.config (In web development, we have web.config)

Student Class:

namespace DotnetConsole
{
  public class Student
  {
    public int ID { get; set; }
    public string Name { get; set; }
  }
}

StudentMap Class

This class is required since fluent api is creating table from the configuration.

public class StudentMap: EntityTypeConfiguration<Student>
  {
    public StudentMap()
    {
      this.ToTable("Student");
      this.Property(s => s.ID).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
      this.Property(s => s.Name);
    }
  }

PracticeContext Class:

  public class PracticeContext: DbContext
  {
    public PracticeContext(): base() {}
    public DbSet<Student> Students { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      base.OnModelCreating(modelBuilder);
      foreach (var assembly in AppDomain.CurrentDomain.GetAssemblies())
      {
        modelBuilder.Configurations.AddFromAssembly(assembly);
      }
    }
  }

DataBaseCall Class:

  public class DataBaseCall
  {
    public static List<Student> GetStudents()
    {
      return Execute((context) =>
      {
        var students = context.Students.ToList();
        if (students != null && students.Count() > 0)
        {
          Console.WriteLine($"Student ID : Student Name");
          foreach (Student student in students)
          {
            Console.WriteLine($"{student.ID} : {student.Name}");
          }
        }
        return students;
      });
    }
    private static TResult Execute<TResult>(Func<PracticeContext, TResult> func)
    {
      try
      {
        return func(new PracticeContext());
      }
      catch (SqlException ex)
      {
        Console.WriteLine(ex);
        throw new Exception("Error Occured.");
      }
    }
}

Program Class

  class Program
  {
    static void Main(string[] args)
    {
      try
      {
        DataBaseCall.GetStudents();
      }
      catch(Exception ex)
      {
        Console.WriteLine("Error occured while performing student operation.", ex);
      }
      Console.ReadKey();
    }
}

App.config file:

Highlighted things are mandatory:


  • entityFramework: Install it from the Nuget Package Manager
  • PracticeContext: The name of the dbcontext class which we have created above.
  • DESKTOP: Name of the machine to which you are connecting for local you may use dot(.)
  • Practice: Name of the database to which you are connecting.
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="PracticeContext" connectionString="Data Source=DESKTOP;Initial Catalog=Practice;User ID=xxx;Password=xxx" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

Output:


Problem

The problem with the above code is that when ever we add any properties to the Student Model the below error will be thrown.

The model backing the PracticeContext context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

To resolve this we can choose either of these two:
  • We need to add the code as below to the Main Method in Program.cs
    • This approach will delete everything from the database and recreate everything again from the scratch.
     static void Main(string[] args)
    {
      try
      {
        Database.SetInitializer(new DropCreateDatabaseIfModelChanges<PracticeContext>())
        DataBaseCall.GetStudents();
      }
      catch(Exception ex)
      {
        Console.WriteLine("Error occured while performing student operation.", ex);
      }
      Console.ReadKey();
    }
  • Or, We can do as below: This approach will only update the database with the latest changes without impacting the existing things.
    •  Run the command in Package Manager Console Enable-Migrations
      • This command has added a Migrations folder to our project. This new folder contains two files:
        • The Configuration class (Configuration.cs)
        • An InitialCreate migration
    • We need to add the code as below to the Main Method in Program.cs
    static void Main(string[] args)
    {
      try
      {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<PracticeContext, Configuration>());
        DataBaseCall.GetStudents();
      }
      catch(Exception ex)
      {
        Console.WriteLine("Error occured while performing student operation.", ex);
      }
      Console.ReadKey();
    }

Configuration.cs

The file code looks as below:

    internal sealed class Configuration : DbMigrationsConfiguration<DotnetConsole.PracticeContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }
        protected override void Seed(DotnetConsole.PracticeContext context)
        {
            //  This method will be called after migrating to the latest version.
            //  You can use the DbSet<T>.AddOrUpdate() helper extension method
            //  to avoid creating duplicate seed data.
        }
    }


Do it with async and await

  • Repository.cs
  public class Repository<T>: IDisposable where T: class
  {
    private PracticeContext context =  null;

    public DbSet<T> Entities => this.context.Set<T>();

    public IQueryable<T> Table => this.Entities;

    public Repository(PracticeContext context)
    {
      this.context = context;
    }

    public async Task<List<T>> InsertListAsync(List<T> entites)
    {
      try
      {
        List<T> t = this.Entities.AddRange(entites).ToList();
        await this.context.SaveChangesAsync();
        return t;
      }
      catch (DbEntityValidationException dbEx)
      {
        var msg = string.Empty;
        foreach (var validationErrors in dbEx.EntityValidationErrors)
        {
          foreach (var validationError in validationErrors.ValidationErrors)
          {
            msg += string.Format("Property: {0} Error: {1}{2}", validationError.PropertyName, validationError.ErrorMessage, Environment.NewLine);
          }
        }
        var fail = new Exception(msg, dbEx);
        //Debug.WriteLine(fail.Message, fail);
        throw fail;
      }
    }

    public void Dispose()
    {
      GC.SuppressFinalize(this);
    }
}

  • DataBaseCall.cs
    public static async Task<List<T>> InsertListAsync<T>(List<T> list) where T: class
    {
      try
      {
        return await ExecuteAsync<T, List<T>>(async (repository) =>
        {
          return await repository.InsertListAsync(list);
        });
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex);
        return null;
      }
    }

     private async static Task<TResult> ExecuteAsync<T, TResult>(Func<Repository<T>, Task<TResult>> func) where T : class
    {
      using (PracticeContext context = new PracticeContext())
      {
        try
        {
          return await func(new Repository<T>(context));
        }
        catch (Exception ex)
        {
          Console.WriteLine(ex);
          throw new Exception("Error Occured.");
        }
      }
    }

Program.cs

     static void Main(string[] args)
    {
      DataBaseCallMethod();
      Console.ReadKey();
    }

    private static async void DataBaseCallMethod()
    {
      try
      {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<PracticeContext, Migrations.Configuration>());
        var students = new List<Student>();
        var results = await DataBaseCall.InsertListAsync(students);
      }
      catch(Exception ex)
      {
        Console.WriteLine("Error occured while performing student operation.", ex.ToString());
      }
    }

Please refer below link for details.

Comments

Popular posts from this blog

npm install on windows 7 python2 not found error and node-sass@3.13.1 error #317

Before I write all, I'd like to say that before the update everything worked well; while updating node from version 7.2.1 to version 9.4.0 and npm from version 5.5.1 to version 5.6.0 in Windows 7 (64-bit) using the installer (https://nodejs.org/en/download/) and installing globally gulp-cli version 2.0.0, as far as npm install starts from the JointsWP folder I get the following error: $ npm install npm WARN deprecated babel-preset-es2015@6.24.1: รฐ  Thanks for using Babel: we recommend using babel-preset-env now: please read babeljs.io/env to update! npm WARN deprecated gulp-util@3.0.8: gulp-util is deprecated - replace it, following the guidelines at https://medium.com/gulpjs/gulp-util-ca3b1f9f9ac5 npm WARN deprecated minimatch@2.0.10: Please update to minimatch 3.0.2 or higher to avoid a RegExp DoS issue > uws@0.14.5 install C:\Users\Bob\Dropbox\Development\vhosts\mongoose-project\vanilla\themes\JointsWP\node_modules\uws > node-gyp rebuild > build_log.txt 2>...

Useful video links with Questions and Answers(.NET, SQL/Database, Azure, DSA, Docker, GIT, AI)

A) AI What is artificial intelligence (AI)? B) .NET SOLID Principles In C# With Examples Design Patterns In C# .NET (2023)   Design Patterns   Software Architecture And Patterns Shiva Kumar - .NET (Youtube)   Multithreading and multitasking Multithreading and asynchronous programming and parallel programming in C# (Youtube)   C) SQL/Database SQL Query Interview Questions - SQL Server Database (Youtube)   ACID Properties Of Transaction In DBMS (Youtube) D) Azure   Modules in this learning path - MSDN Susanth Sutheesh (Youtube)   E) DSA   Strivers A2Z DSA Course/Sheet F) Docker   Docker - Everything You Need To Know G) GIT   Start with Git & GitHub in Visual Studio Misc https://www.youtube.com/watch?v=aaUInV445BY Salary Negotiation - 10 tips on how to negotiate a Higher Salary    

Buying my buddy - Laptop

 Brief  I am planning to buy a new laptop and I am putting everything here which I come across while looking for best laptop which fits my requirements. My Requirements  Programming: .NET, Angular, SQL, Visual Studio, SQL Server Video Editing Country where I live is Nepal Able to use at least for 5 years Considering the above requirements, I will going with the Dell laptop though in Nepal Acer is quite famous and I will highlight why choose Dell over other laptop.  Before Buying, let's understand the terminology RAM SSD Nits Cooling Graphics Thunderbolt USB WIFI Screen SRGB Laptop meeting my requirements Below configuration will do my job smoothly for at least 5 years and they are: 16GB RAM DDR5 SSD Laptops Meeting My Requirments are: https://www.flipkart.Lenovo IdeaPad Pro 5com/lenovo-ideapad-pro-5-intel-core-ultra-9-185h-32-gb-1-tb-ssd-windows-11-home-14imh9-thin-light-laptop/p/itm1f4865cbfe7ea?pid=COMGZN7RRHARKTDE&lid=LSTCOMGZN7RRHARKTDE4G0GAP&marketplace=...