csharp: EF6 Relationship

sql script:

--查询lukfookhr 的所有表
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.88.1,1435;User ID=geovindu;Password=000;').lukfookhr.information_schema.TABLES



exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure


--查询数据数据库
SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.88.1,1435;User ID=geovindu;Password=000;').lukfookhr.sys.databases



--dbo.BillingDetails
--BillingDetailId int identity
--Owner
--Number
--BankName
--Swift
--CardType int
--ExpiryMonth
--ExpiryYear
--Discriminator 

IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].BillingDetails') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE BillingDetails 
GO
create table BillingDetails
(
	 BillingDetailId INT IDENTITY(1,1) PRIMARY KEY,
	 [Owner] nvarchar(50) not  null,
	 Number nvarchar(50)  null,
	--BankName nvarchar(50)  null,
	--Swift nvarchar(50)  null,
	--CardType int null,
	--ExpiryMonth nvarchar(50)  null,
	--ExpiryYear nvarchar(50)  null,
	Discriminator  nvarchar(50)  null
)
go


--BankAccount BankName  Swift
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].BankAccount') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE BankAccount 
GO
create table BankAccount
(
	 Id INT IDENTITY(1,1) PRIMARY KEY,
	 BankName   nvarchar(50)  null,
	 Swift nvarchar(50)  null
)
go



--CreditCard  CardType ExpiryMonth  ExpiryYear
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].CreditCard') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE CreditCard 
GO
create table CreditCard
(
	 Id INT IDENTITY(1,1) PRIMARY KEY,
	 CardType  nvarchar(50)  null,
	 ExpiryMonth  nvarchar(50)  null, 
	 ExpiryYear  nvarchar(50)  null

)
go



--dbo.Categories  
--CategoryId int
--CategoryName 50
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Categories') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE Categories 
GO
create table Categories
(
	 CategoryId INT IDENTITY(1,1) PRIMARY KEY,
	 CategoryName nvarchar(50) not null
)
go



--dbo.Products
--ProductId int identity
--ProductName 50
--CategoryId int  dbo.Categories CategoryId
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE Products 
GO
create table Products
(
	ProductId INT IDENTITY(1,1) PRIMARY KEY,
	ProductName nvarchar(50) not null,
	CategoryId int
		FOREIGN KEY REFERENCES Categories(CategoryId)

)
go


--dbo.Customers
--Id int identity
--Name 50
--Email  20
--CreatedTime date
--ModifiedTime date
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Customers') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE Customers 
GO
create table Customers
(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	[Name] nvarchar(50) not null,
	Email nvarchar(20) null,
	CreatedTime date null,
	ModifiedTime date null
)
go

insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('geovindu','geovindu@163.com' ,getdate(),getdate())
go

insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('test','geovindu@163.com' ,getdate(),getdate())
go

insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('江城','geovindu@163.com' ,getdate(),getdate())
go

insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('保中','geovindu@163.com' ,getdate(),getdate())
go

insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('王二','geovindu@163.com' ,getdate(),getdate())
go

insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('李三','geovindu@163.com' ,getdate(),getdate())
go


insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('张四','geovindu@163.com' ,getdate(),getdate())
go


insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('赵五','geovindu@163.com' ,getdate(),getdate())
go


insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('陈六','geovindu@163.com' ,getdate(),getdate())
go



insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('刘七','geovindu@163.com' ,getdate(),getdate())
go



insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('徐八','geovindu@163.com' ,getdate(),getdate())
go

insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('何九','geovindu@163.com' ,getdate(),getdate())
go

select * from Customers
go



--dbo.Orders
--Id int identity
--Quantity int
-- Code 400
--Price Decimal 18,4
--CustomerId int dbo.Customers CustomerId
--CreatedTime
--ModifiedTime

IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Orders') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE Orders 
GO
create table Orders
(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	Quantity int null,
	Code nvarchar(400) null,
	Price decimal(18,4) null,
	CustomerId int 
		FOREIGN KEY REFERENCES Customers(Id),
	CreatedTime date null,
	ModifiedTime date null
)
go

insert into Orders(Quantity,Code,Price,CustomerId,CreatedTime,ModifiedTime) values(10,N'4008',250.00,1,getdate(),getdate())
go

select * from Orders
go



--dbo.Students
--Id int identity
--Name 
--Age byte
--CreatedTime date
-- ModifiedTime date
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Students') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE Students 
GO
create table Students
(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	[Name] nvarchar(50) not null,
	Age tinyint  null,
	CreatedTime date null,
	ModifiedTime date null
)
go

insert into Students([Name],Age,CreatedTime,ModifiedTime) values('sibodu',12,getdate(),getdate())
go

select * from Students
go

--StudentContact  ContactNumber

IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].StudentContact') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE StudentContact 
GO
create table StudentContact
(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	StudentId int
	   FOREIGN KEY REFERENCES Students(Id),
	ContactNumber nvarchar(50) null
)
go











-- dbo.Courses
--Id  int identity
--Name
--MaximumStrength int
--CreatedTime
--ModifiedTime
--
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Courses') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE Courses 
GO
create table Courses
(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	[Name] nvarchar(50) not null,
	MaximumStrength int null,
	CreatedTime date null,
	ModifiedTime date null
)
go

insert into Courses([Name],MaximumStrength,CreatedTime,ModifiedTime) values('语文',25,getdate(),getdate())
go


select * from Courses
go



-- DropForeignKey("dbo.Orders", "CustomerId", "dbo.Customers");
-- DropForeignKey("dbo.Products", "CategoryId", "dbo.Categories");

　　

using System;
using System.ComponentModel.DataAnnotations;


namespace EntityFramework6.Enity
{


    /// <summary>
    /// 
    /// </summary>
    public abstract class BaseEntity
    {

        /// <summary>
        /// 
        /// </summary>
        [Display(Name = "ID")]
        public int Id { get; set; }

        /// <summary>
        /// 
        /// </summary>

        [Display(Name = "創建時間")]
        public DateTime CreatedTime { get; set; }
        /// <summary>
        /// 
        /// </summary>

        [Display(Name = "修改時間")]
        public DateTime ModifiedTime { get; set; }
    }
}


using EntityFramework6.Enity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;


namespace EntityFramework6.Entity
{
    /// <summary>
    /// 
    /// </summary>
    public class Course : BaseEntity
    {

        /// <summary>
        /// 課程名称
        /// </summary>
        [Display(Name = "課程名称")]
        public string Name { get; set; }
        /// <summary>
        /// 課時時長
        /// </summary>

        [Display(Name = "課時時長")]
        public int MaximumStrength { get; set; }

        /// <summary>
        /// 学生
        /// </summary>
        [Display(Name = "学生")]
        public virtual ICollection<Student> Students { get; set; }
    }
}


using EntityFramework6.Enity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Core.Metadata;

//using Microsoft.EntityFrameworkCore.Metadata;

namespace EntityFramework6.Entity
{

    /// <summary>
    /// 
    /// </summary>
    public class Student : BaseEntity
    {

        [Display(Name = "姓名")]
        public string Name { get; set; }

        [Display(Name = "年龄")]
        public byte Age { get; set; }


        /// <summary>
        /// 课程
        /// </summary>
        [Display(Name = "课程")]
        public virtual ICollection<Course> Courses { get; set; }

        /// <summary>
        /// 
        /// </summary>
        [Display(Name = "联系方式")]
        public virtual StudentContact Contact { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EntityFramework6.Enity;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Core.Metadata;
using System.ComponentModel.DataAnnotations.Schema;

//using Microsoft.EntityFrameworkCore.Metadata;

namespace EntityFramework6.Entity
{

    /// <summary>
    /// 
    /// </summary>
    public class StudentContact
    {

        /// <summary>
        /// 
        /// </summary>
        [Display(Name = "ID")]
        public int Id { get; set; }
        /// <summary>
        /// 联系电话
        /// </summary>

        [Display(Name = "联系电话")]
  
        public string ContactNumber { get; set; }

        /// <summary>
        /// 学生ID
        /// </summary>
        [Display(Name = "学生ID")]
        [ForeignKey("Student")]
        public int StudentId { get; set; }

        /// <summary>
        /// 学生
        /// </summary>
        [Display(Name = "学生")]
        public virtual Student Student {get;set;}


    }
}


using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.ComponentModel.DataAnnotations;



namespace EntityFramework6.Enity
{

    /// <summary>
    /// 顾客
    /// </summary>
    public partial class Customer : BaseEntity
    {
        /// <summary>
        /// 
        /// </summary>
        [Display(Name = "标题")]
        public string Name { get; set; }
        /// <summary>
        /// 
        /// </summary>
        [Display(Name = "邮件")]
        public string Email { get; set; }

        [Display(Name = "訂單")]

        public virtual ICollection<Order> Orders { get; set; }
    }
}

using System;
using System.ComponentModel.DataAnnotations;


namespace EntityFramework6.Enity
{

    /// <summary>
    /// 
    /// </summary>
    public class Order : BaseEntity
    {

        [Display(Name = "数量")]
        public int Quantity { get; set; }
        [Display(Name = "编码")]
        public string Code { get; set; }
        [Display(Name = "价格")]
        public decimal Price { get; set; }
        [Display(Name = "客户ID")]
        public int CustomerId { get; set; }
        [Display(Name = "客户")]
        public virtual Customer Customer { get; set; }
    }
}


using System.ComponentModel.DataAnnotations;

namespace EntityFramework6.Entity
{

    /// <summary>
    /// 
    /// </summary>
    public class Product
    {

        /// <summary>
        /// 
        /// </summary>
        [Display(Name = "id")]
        public int ProductId { get; set; }

        /// <summary>
        /// 
        /// </summary>
        [Display(Name = "产品名称")]
        public string ProductName { get; set; }

        [Display(Name = "类型ID")]
        public int CategoryId { get; set; }
        [Display(Name = "类型")]
        public virtual Category Category { get; set; }
    }
}

　

map:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using EntityFramework6.Entity;
//https://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx


namespace EntityFramework6.Map
{

    /// <summary>
    /// 
    /// </summary>
    public class StudentContactMap:EntityTypeConfiguration<StudentContact>
    {


        public StudentContactMap()
        {
            //table
            ToTable("StudentContact");

            //key
            HasKey(t => t.Id);

            //fields
            Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(x=>x.StudentId)
                .HasColumnName("StudentId")
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
            Property(x => x.ContactNumber);

            //relationship one-to-one 一对一
            HasRequired(s => s.Student).WithOptional(l => l.Contact);
        }

    }
}



using EntityFramework6.Entity;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;

//https://entityframework.net/one-to-one-relationship
//https://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx
//https://github.com/dotnet/ef6
//https://docs.microsoft.com/en-us/ef/ef6/


namespace EntityFramework6.Map
{

    /// <summary>
    /// geovindu
    /// </summary>
    public class StudentMap : EntityTypeConfiguration<Student>
    {
        /// <summary>
        /// 
        /// </summary>
        public StudentMap()
        {
            //table  
            ToTable("Students");

            //key
            HasKey(t => t.Id);

            //fields
            Property(x => x.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(x => x.Name).HasColumnType("VARCHAR").HasMaxLength(50);
            Property(x => x.Age);
            Property(x => x.CreatedTime);
            Property(x => x.ModifiedTime);

            //relationship Many-to-Many Relationishhip 多对多
            HasMany(t => t.Courses).WithMany(c => c.Students)
                .Map(t => t.ToTable("SutdentCourcese")
                .MapLeftKey("StudentId")
                .MapRightKey("CourseId"));

            //realationship One-to-One 一对一

            HasOptional(x => x.Contact)
                .WithOptionalDependent(l=>l.Student);


        }
    }
}


using EntityFramework6.Entity;
using System.Data.Entity.ModelConfiguration;
using System.ComponentModel.DataAnnotations.Schema;



namespace EntityFramework6.Map
{

    /// <summary>
    /// geovindu
    /// </summary>
    public class CourseMap : EntityTypeConfiguration<Course>
    {
        public CourseMap()
        {
            //table
            ToTable("Courses");

            //key
            HasKey(k => k.Id);

            //property
            Property(t => t.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
            Property(t => t.Name).HasColumnType("VARCHAR").HasMaxLength(50);
            Property(t => t.MaximumStrength);
            Property(t => t.CreatedTime);
            Property(t => t.ModifiedTime);

            


        }
    }
}


using EntityFramework6.Enity;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;

namespace EntityFramework6.Map
{


    /// <summary>
    /// geovindu,Geovin Du
    /// 
    /// </summary>
    public class OrderMap : EntityTypeConfiguration<Order>
    {

        /// <summary>
        /// 
        /// </summary>
        public OrderMap()
        {
            //table  
            ToTable("Orders");

            //key  
            HasKey(t => t.Id)
                .Property(p => p.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            //fields  
            Property(t => t.Quantity);
            Property(t => t.Price).HasPrecision(18,4);
            Property(t => t.CustomerId);
            Property(t => t.CreatedTime);
            Property(t => t.ModifiedTime);
            Property(t => t.Code).HasMaxLength(400);

            //relationship  One-to-Many Relationship 一对多
            HasRequired(t => t.Customer)
                .WithMany(c => c.Orders)
                .HasForeignKey(t => t.CustomerId)
                .WillCascadeOnDelete(false);
        }
    }
}


using EntityFramework6.Enity;
using System.Data.Entity.ModelConfiguration;
using System.ComponentModel.DataAnnotations.Schema;



namespace EntityFramework6.Map
{

    /// <summary>
    /// 
    /// </summary>
    public class CustomerMap : EntityTypeConfiguration<Customer>
    {
        public CustomerMap()
        {
            //table  
            ToTable("Customers");

            //key
            HasKey(t => t.Id);

            //properties  
            Property(t => t.Name).HasColumnType("VARCHAR").HasMaxLength(50).IsRequired();
            Property(t => t.Email).HasColumnType("VARCHAR").HasMaxLength(20).IsRequired();
            Property(t => t.CreatedTime).IsRequired();
            Property(t => t.ModifiedTime).IsRequired();
        }
    }
}


using EntityFramework6.Entity;
using System.Data.Entity.ModelConfiguration;
using System.ComponentModel.DataAnnotations.Schema;


namespace EntityFramework6.Map
{

    /// <summary>
    /// 
    /// </summary>
    public class CategoryMap : EntityTypeConfiguration<Category>
    {
        public CategoryMap()
        {
            ToTable("Categories");

            HasKey(k => k.CategoryId);

            Property(p => p.CategoryName).HasColumnType("VARCHAR").HasMaxLength(50);
            //
            HasMany(p => p.Products).WithRequired(c => c.Category).HasForeignKey(k => k.CategoryId);
        }
    }
}


using EntityFramework6.Entity;
using System.Data.Entity.ModelConfiguration;
using System.ComponentModel.DataAnnotations.Schema;



namespace EntityFramework6.Map
{

    /// <summary>
    /// 
    /// </summary>
    public class ProductMap : EntityTypeConfiguration<Product>
    {
        public ProductMap()
        {
            ToTable("Products");

            HasKey(k => k.ProductId);

            Property(p => p.ProductName).HasColumnType("VARCHAR").HasMaxLength(50);
        }
    }
}

　　

using EntityFramework6.Enity;
using System;
using System.Linq;
using System.Data;
using System.Data.SqlClient;

using System.Data.Entity.ModelConfiguration;

namespace EntityFramework6
{

    /// <summary>
    /// geovindu,Geovin Du
    /// 
    /// </summary>
    public class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new EfDbContext())
            {
                ctx.Database.Log = Console.WriteLine;

                // var customers = ctx.Customers;

                //var customer = customers.FirstOrDefault(d => d.Id == 1); // customers.FindAsync(1).Result;
                //Console.WriteLine(customer.Name);

                //bool contains = customers.Where(c => c.Name == "geovindu").Contains(customer);


                //添加
                //var addcustomer = new Customer()
                //{
                //    Name = "涂聚文",
                //    Email = "463588883@qq.com",
                //    CreatedTime = DateTime.Now,
                //    ModifiedTime = DateTime.Now
                //};
                //ctx.Customers.Add(addcustomer);
                //int k=ctx.SaveChanges();


                //修改
                // var editcustomer = new Customer()
                // {
                //   Id =1,
                //   Name="涂年生",
                //   Email="463588883@qq.com",
                //   CreatedTime=DateTime.Now,
                //   ModifiedTime=DateTime.Now

                // };
                // ctx.Entry(editcustomer).State=System.Data.Entity.EntityState.Modified;
                //int k=ctx.SaveChanges();
                // if (k > 0)
                //     Console.WriteLine("ok,eidt");

                //删除
                //var delcustomer = new Customer()
                //{
                //    Id= 3
                //};

                //ctx.Entry(delcustomer).State = System.Data.Entity.EntityState.Deleted;
                //int k = ctx.SaveChanges();
                //if (k > 0)
                //    Console.WriteLine("ok del");


                //查询集合
                var customers = ctx.Customers;

                //var names = customers
                //    .Select(x => x.Name)
                //    //.Take(10)
                //    .OrderBy(d=>1)
                //    .Skip(5)                    
                //    .ToList();

                var names = customers
                    .Select(x => x.Name)
                    .Take(10)
                    .OrderBy(d => 1)
                    //.Skip(5)
                    .ToList();

                foreach (var name in names)
                {
                    Console.WriteLine(name+"\n\t");
                }

                //原始查询
                var cus = ctx.Database.SqlQuery<Customer>("SELECT * FROM dbo.Customers").ToList();

                foreach (var cs in cus)
                {
                    Console.WriteLine("name:"+cs.Name + "\n\t");
                }

                var id = 1;
                var par = new SqlParameter[]
                    { 
                        new SqlParameter(){ ParameterName="@id",SqlDbType=SqlDbType.Int,Value=id },
                    };
                var cust = ctx.Database.SqlQuery<Customer>("SELECT * FROM dbo.Customers where id=@id", par).ToList();




            }
            Console.ReadKey();
        }
    }
}