C# – Foreign Keys in Entity Framework – Code First – Query with Linq

Posted on February 12, 2019 by

In this article, I present an Entity Framework example, building two tables with foreign keys, using code first. The sample I am using is building over the previous article for EF.

Pretty much, when you are running your application, if you receive the fancy message during C# execution “Cannot drop database because it is currently in use“, although you are having Database.SetInitializer(new DropCreateDatabaseAlways<SomeDbContext>()); then it is time to run this query:

USE MASTER
ALTER DATABASE [SomeDbProject.DbContext] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [SomeDbProject.DbContext]

USE MASTER

ALTER DATABASE [SomeDbProject.DbContext] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [SomeDbProject.DbContext]

The easiest way to execute it is to select the DB from the SQL Server Object Explorer, create a new SQL document and press Ctrl+Shift+E:

Start

At first we need 2 classes – Company.cs and Car.cs. The properties of these classes would be the columns in the tables corresponding to these in our DB:

using System.Collections.Generic;

namespace SomeDbProject
{
    class Company
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Owner { get; set; }
        public int YearEstablished { get; set; }
        public virtual ICollection Cars { get; set; }
    }
}

using System.Collections.Generic;

namespace SomeDbProject

{

class Company

{

public int Id { get; set; }

public string Name { get; set; }

public string Owner { get; set; }

public int YearEstablished { get; set; }

public virtual ICollection Cars { get; set; }

}

class Car
{
    public int Id { get; set; }
    public string Model { get; set; }
    public decimal Price { get; set; }
    public string Origin { get; set; }
    public int Year { get; set; }
    public virtual int? CompanyId {get; set;}
    public virtual Company Company { get; set; }
}

class Car

{

public int Id { get; set; }

public string Model { get; set; }

public decimal Price { get; set; }

public string Origin { get; set; }

public int Year { get; set; }

public virtual int? CompanyId {get; set;}

public virtual Company Company { get; set; }

}

In order to tell our DbContext, that these two classes are actually DB tables and should be created, a DbContext class is needed. Or something, that inherits it:

namespace SomeDbProject
{
    using System.Data.Entity;
    class SomeDbContext : DbContext
    {
        public SomeDbContext() : base("name=SomeDbProjectConnectionString")
        {
            Database.SetInitializer(new DropCreateDatabaseAlways());
        }
        public DbSet  Cars { get; set; }
        public DbSet Companies { get; set; }
    }
}

namespace SomeDbProject

{

using System.Data.Entity;

class SomeDbContext : DbContext

{

public SomeDbContext() : base("name=SomeDbProjectConnectionString")

{

Database.SetInitializer(new DropCreateDatabaseAlways());

}

public DbSet Cars { get; set; }

public DbSet Companies { get; set; }

}

So, what can we do with these beautiful classes? We may ask EF to create a DB and make the classes a table. Then EF may enter some data in them with loops. Quite an easy and self-explanatory method:

            SomeDbContext context = new SomeDbContext();
            int companiesToMake = 20;
            int carsPerCompany = 5;
            int carsToMake = companiesToMake * carsPerCompany;

            for (int i = 0; i < carsToMake; i++)
            {
                Car newCar = new Car();
                newCar.Model = newCar.GetModel(i);
                newCar.Origin = newCar.GetOrigin(i);
                newCar.Price = newCar.GetPrice(i);
                newCar.Year = newCar.GetYear(i);
                context.Cars.Add(newCar);
            }
            context.SaveChanges();

            for (int i = 0; i < companiesToMake; i++)
            {
                List companyCars = new List();
                for (int x = 0; x < carsPerCompany; x++)
                {
                    int idOfCar = i * 5 + x + 1;
                    Car currentCar = new Car();
                    currentCar = context.Cars.FirstOrDefault(car => car.Id == idOfCar);
                    companyCars.Add(currentCar);
                }

                context.Companies.Add(new Company
                {
                    Name = $"Company {i.ToString()}",
                    Owner = $"Owner {i.ToString()}",
                    YearEstablished = 1999,
                    Cars = companyCars,
                });
            }
            context.SaveChanges();

SomeDbContext context = new SomeDbContext();

int companiesToMake = 20;

int carsPerCompany = 5;

int carsToMake = companiesToMake * carsPerCompany;

for (int i = 0; i < carsToMake; i++)

{

Car newCar = new Car();

newCar.Model = newCar.GetModel(i);

newCar.Origin = newCar.GetOrigin(i);

newCar.Price = newCar.GetPrice(i);

newCar.Year = newCar.GetYear(i);

context.Cars.Add(newCar);

}

context.SaveChanges();

for (int i = 0; i < companiesToMake; i++)

{

List companyCars = new List();

for (int x = 0; x < carsPerCompany; x++)

{

int idOfCar = i * 5 + x + 1;

Car currentCar = new Car();

currentCar = context.Cars.FirstOrDefault(car => car.Id == idOfCar);

companyCars.Add(currentCar);

}

context.Companies.Add(new Company

{

Name = $"Company {i.ToString()}",

Owner = $"Owner {i.ToString()}",

YearEstablished = 1999,

Cars = companyCars,

});

}

context.SaveChanges();

The query in Linq is interesting. We need to unite two tables and print all the cars in all the companies, which are with country of origin Bulgaria or Italy. This is the query sample:

            Console.WriteLine("LINQ:\n");
            var queryCars =
                    from ca in context.Cars
                    from co in context.Companies
                    where co.Id == ca.CompanyId &&
                        (ca.Origin == "Bulgaria" || ca.Origin == "Italy")
                    select new
                    {
                        CarId = ca.Id,
                        companyId = co.Id,
                        companyName = co.Name,
                        carModel = ca.Model,
                        carPrice = ca.Price,
                        carCountry = ca.Origin

                    };

            foreach (var car in queryCars)
            {
                Console.WriteLine($"{car.companyId} - " +
                                    $"{car.companyName} - " +
                                    $"{car.CarId} - " +
                                    $"{car.carModel} - " +
                                    $"{car.carPrice} - " +
                                    $"{car.carCountry}");
            }

Console.WriteLine("LINQ:\n");

var queryCars =

from ca in context.Cars

from co in context.Companies

where co.Id == ca.CompanyId &&

(ca.Origin == "Bulgaria" || ca.Origin == "Italy")

select new

{

CarId = ca.Id,

companyId = co.Id,

companyName = co.Name,

carModel = ca.Model,

carPrice = ca.Price,

carCountry = ca.Origin

};

foreach (var car in queryCars)

{

Console.WriteLine($"{car.companyId} - " +

$"{car.companyName} - " +

$"{car.CarId} - " +

$"{car.carModel} - " +

$"{car.carPrice} - " +

$"{car.carCountry}");

}

And this is what it prints:

Pretty much this is all. The whole code is in GitHub here.

Enjoy it 🙂

Tagged with: C#, code first, Foreign Keys, Foreign Keys in Entity Framework

C# – Foreign Keys in Entity Framework – Code First – Query with Linq

Start

Related posts: