Showing posts with label Entity Framework. Show all posts
Showing posts with label Entity Framework. Show all posts

Saturday, January 25, 2020

Run query inside a Transaction - Entity Framework

In Entity Framework you have to run the code with in a DbContextTransaction scope. In this example we run within the DbContextTransaction scope, Get a lock & finally commit at the end.

public bool MySampleMethod(Model instance)
{
using (DbContextTransaction scope = _context.Database.BeginTransaction())
{
bool sent;
_context.Database.ExecuteSqlCommand("SELECT TOP 1 Id FROM MySampleTable WITH (TABLOCKX, HOLDLOCK)");

sent = _context.MySampleTable.Any(n => n.Guid == instance.Guid &&
n.PublishedDate ==
instance.PublishedDate &&
n.UserId == instance.UserId &&
n.RegistrationId ==
instance.RegistrationId);

if (!sent)
{
DoFurtherProcessing(instance);
}
scope.Commit();
}
return sent;
}

Saturday, September 21, 2019

Entity Framework - How to migrate when there are two database contexts in your solution

Here I will explain how to migrate when there are two separate db contexts in two separate projects in the same solution.

The format for migration commands are as follows:

enable-migrations -ContextTypeName <DbContext-Name-with-Namespaces> -MigrationsDirectory:<Migrations-Directory-Name>

DbContext-Name-with-Namespaces - This is the full path to the database context class (including namespace)

Add-Migration -configuration <DbContext-Migrations-Configuration-Class-with-Namespaces> <Migrations-Name>

DbContext-Migrations-Configuration-Class-with-Namespaces - This is the full path to the configuration class (including namespace), which was created in the previous step.

Update-Database -configuration <DbContext-Migrations-Configuration-Class-with-Namespaces> -Verbose

Example:

1) In the Nuget Package Manager Console, make sure to select the "Default Project". That is the project which the  migrations is going to happen.

2) Also if the startup project contains a App.Config, you need to add the ConnectionString 's for the two context there as well.

3) Enable migrations

PM> Enable-migrations -ContextTypeName:My.Notification.ServicesMock.Data.MyMockContext -MigrationsDirectory:MyMigrations 
Result:
Checking if the context targets an existing database...
Code First Migrations enabled for project My.Notification.ServicesMock.

Now you will see a migrations directory been created in your project as "MyMigrations", which contains the configuration class. Later on, the migrations will be added to that folder.

4) Add Migration

PM> Add-Migration -configuration My.Notification.ServicesMock.MyMigrations.Configuration "Initial"
Result:
Scaffolding migration 'Initial'.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration Initial' again.

5) Update database

PM> Update-Database -configuration My.Notification.ServicesMock.MyMigrations.Configuration -verbose
Result:
Using StartUp project '...'.
Using NuGet project '...'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.

Applying explicit migrations: [61401174_Initial].
Running Seed method.

Your migrations  are complete now. Have a look in the Db.

Saturday, July 27, 2019

Entity Framework - Adding Auto Increment Column [Code First]

Use the attribute "DatabaseGenerated"

Example:

    public class Device
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int DeviceId { get; set; }
        public string Language { get; set; }
        public string RegistrationId { get; set; }
        public string DeviceType { get; set; }
        public int UserNo { get; set; }
    }

Saturday, December 1, 2018

Querying Database tables using Linq


Join two tables. Check for boolean values in columns. Select multiple columns 

var eventFBQuestions = (from FQ in db.FeedbackQuestions join FT in db.FeedbackTypes on FQ.FeedbackTypeId equals FT.Id where (!FQ.Archived && !FT.Archived && FT.Name == "Event") select new { FQ.Id, FQ.Question }).ToList();

Get the count

var countForOne = (from Fb in db.Feedbacks where (Fb.FeedbackQuestionId == question.Id && Fb.FeedbackScore == 1) select Fb.Id).Count();


Join more than two tables. Alter the select columns by concatenating the select fields

            var sessions = (from s in db.Sessions
                        join t in db.Tracks on s.TrackId equals t.Id
                        join e in db.Events on t.EventId equals e.Id
                        where (!s.Archived && !t.Archived && !e.Archived)
                        select new {s.Id, Name = t.Name + " - " +  s.Name }).ToList();

same thing into a dictionary

            var sessions = (from s in db.Sessions
                        join t in db.Tracks on s.TrackId equals t.Id
                        join e in db.Events on t.EventId equals e.Id
                        where (!s.Archived && !t.Archived && !e.Archived)
                        select new {ID=s.Id.ToString(), NAME = t.Name + " - " +  s.Name }).ToDictionary(dic => dic.ID, dic => dic.NAME);

Tuesday, March 21, 2017

Migrate code first solution - Entity Framework

Fire up package manager console
























Type Enable-Migrations









A Migration folder will get created in your solution


Do the code changes you need to do for the entities. In my case I have added a new property as BirthDate to my Student class

Then in the Package Manager Window, type Add-Migration with a custom name which you would like to be referred to on the solution. In the solution explorer window, you would see a new file which has been created now.



Then run the update command to update the DB like below. Your changes will get reflected in the Database