In this article I am going to demonstrate how to configure
one-to-may and many-to-many relationship using Entity Framework code first
approach, by taking a rich example in asp.net MVC.
As you see in the above figure, we are going to configure One-to-Many
relationship between Post and Category as many posts in one
category and Many-to-Many relationship between Post and Tag
entity classes via PostTagMap entity.
Entity Framework knows that there is a many-to-many
relationship between two classes when two classes have collections of one
another’s type.
Create Post class in Model folder:
public class Post
{
public int
PostID { get; set; }
public string
Title { get; set; }
public string
Description { get; set; }
public DateTime
CreationDate { get; set; }
public virtual Category
Category { get; set; }
public int
CategoryID { get; set; }
public virtual ICollection<Tag>
Tags { get; set; }
}
Create Category class in the same Model folder:
public class Category
{
public int
CategoryID { get; set; }
public string Name
{ get; set; }
public virtual ICollection<Post>
Posts { get; set; }
}
Post entity class has reference property of Category
class with CategoryID foreign key property and Category class has
collection property for Posts. So this DataAnnotation will result in One-to-Many
relationship.
Create Tag class in Model folder:
public class Tag
{
public int
TagID { get; set; }
public string Name
{ get; set; }
public virtual ICollection<Post>
Posts { get; set; }
}
Post class should have collection navigation property for Tags
and Tag should have collection navigation property for Posts which will
create Many-to-Many relationship between post and tag.
Create PostDbContext class to create entities in
database and configuring many-to-many relationship using fluent API.
public class PostDbContext : DbContext
{
public
PostDbContext()
{
Database.SetInitializer(new PostDbContextInitializer());
}
protected override void
OnModelCreating(DbModelBuilder modelBuilder)
{
// overriding pluralize convention
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
//
configuring many-to-may relationship between Post and Tag
modelBuilder.Entity<Post>().HasMany<Tag>(s
=> s.Tags).WithMany(c => c.Posts).Map(m =>
{
m.MapLeftKey("PostID");
m.MapRightKey("TagID");
m.ToTable("PostTagMap");
});
base.OnModelCreating(modelBuilder);
}
public DbSet<Post>
Post { get; set; }
public DbSet<Tag>
Tag { get; set; }
public DbSet<Category>
Category { get; set; }
}
As you can see in above code that we are mapping left key
(key column of Post class) with “PostID” and right key (key column of
Tag class) with “TagID” of table “PostTagMap”.
This will create new joining table “PostTagMap” with
two primary key (composite key) which is also foreign key of Post and Tag
table.
Create PostDbContextInitializer class to insert some
sample data in order to test our application:
public class PostDbContextInitializer : DropCreateDatabaseIfModelChanges<PostDbContext>
{
protected override void
Seed(PostDbContext context)
{
List<Category>
listCategory = new List<Category>
{
new Category {
Name = "Programming" },
new Category {
Name = "Designing" },
new Category {
Name = "Database" },
};
List<Tag>
listTag = new List<Tag>
{
new Tag {
Name = "Csharp" },
new Tag {
Name = "Asp.Net" },
new Tag {
Name = "Sencha Touch" },
new Tag {
Name = "MVC" },
new Tag {
Name = "SqlServer" },
new Tag {
Name = "Oracle" },
new Tag {
Name = "Bootstrap" },
new Tag {
Name = "Jquery" },
};
List<Post>
listPost = new List<Post>
{
new Post {
Title = "List Paging in Sencha Touch",
Description = "In this one I am going to add one more
important and most used functionality i.e. paging in sencha touch List.",
CreationDate = DateTime.Now, Category =
listCategory.Find(m => m.Name.Equals("Programming")),
Tags = listTag.Where(x => x.Name.Equals("Sencha Touch") ||
x.Name.Equals("Asp.Net")).ToList()
},
new Post {
Title = "CRUD Operation using Sencha Touch and ASP.Net MVC Web
API", Description = "CRUD Operation using
Sencha Touch and ASP.Net MVC Web API In this article I am going to explain and
demonstrate how to create", CreationDate = DateTime.Now,
Category = listCategory.Find(m => m.Name.Equals("Programming")),
Tags = listTag.Where(x => x.Name.Equals("Sencha Touch") ||
x.Name.Equals("Asp.Net") ||
x.Name.Equals("MVC") || x.Name.Equals("Csharp")).ToList()
},
new Post {
Title = "Union Example in SQL Server",
Description = "In this article I am going to explain a
use of union operator in SQL Server Database with a real life scenario and
example. The UNION operator is used to combine the result-set of two or more
SELECT statements.", CreationDate = DateTime.Now,
Category = listCategory.Find(m => m.Name.Equals("Database")),Tags
= listTag.Where(x => x.Name.Equals("SqlServer") ||
x.Name.Equals("Oracle")).ToList()
},
new Post {
Title = "Pivot with Dynamic columns in SQL Server",
Description = "Pivot with Dynamic columns in SQL Server
In this article I will present how we can write a Dynamic PIVOT.",
CreationDate = DateTime.Now, Category =
listCategory.Find(m => m.Name.Equals("Database")),
Tags = listTag.Where(x => x.Name.Equals("SqlServer") ||
x.Name.Equals("Oracle")).ToList()
},
};
listCategory.ForEach(m =>
{
context.Category.Add(m);
});
context.SaveChanges();
listTag.ForEach(m =>
{
context.Tag.Add(m);
});
context.SaveChanges();
listPost.ForEach(m =>
{
context.Post.Add(m);
});
context.SaveChanges();
base.Seed(context);
}
}
Create a PostModel class in Model folder:
public class PostModel
{
public IEnumerable<Post>
Posts { get; set; }
public IEnumerable<Category>
Categories { get; set; }
public IEnumerable<Tag>
Tags { get; set; }
}
Create a HomeController in controller folder and edit
the code as given below:
public class HomeController : Controller
{
PostDbContext db =
new PostDbContext();
public ActionResult
Index()
{
var
posts = db.Post.OrderByDescending(m => m.CreationDate).ToList();
var tags
= db.Tag.ToList();
var
categories = db.Category.ToList();
var
model = new PostModel
{
Posts
= posts,
Tags =
tags,
Categories = categories
};
return
View(model);
}
}
Add a _Layout.cshtml in Views/Shared folder and
replace the code as given below:
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>@ViewBag.Title</title>
<link href="~/Content/bootstrap-3.1.1-dist/css/bootstrap.min.css" rel="stylesheet" />
<link href="~/Content/bootstrap-3.1.1-dist/css/bootstrap-theme.min.css" rel="stylesheet" />
<style type="text/css">
body
{
margin-top: 75px;
}
footer
{
margin: 10px 0;
}
</style>
</head>
<body>
<header class="navbar
navbar-inverse navbar-fixed-top bs-docs-nav" role="banner">
<div class="container">
<div class="navbar-header">
<button class="navbar-toggle" type="button" data-toggle="collapse" data-target=".bs-navbar-collapse">
<span class="sr-only">Toggle
Navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a href="index.html" class="navbar-brand">Posts</a>
</div>
<nav class="collapse
navbar-collapse bs-navbar-collapse" role="navigation">
<form class="navbar-form
navbar-right" role="search">
<div class="form-group">
<input type="text" class="form-control" placeholder="Search" />
</div>
</form>
<ul class="nav
navbar-nav">
<li class="active"><a href="index.html">Home</a></li>
<li><a href="contact.html">Contact</a></li>
<li><a href="about.html">About</a></li>
</ul>
</nav>
</div>
</header>
<div id="body" class="container">
@RenderBody()
</div>
<footer>
<div class="container">
<hr />
<p class="text-center">Copyright
© ABC 2014. All rights reserved.</p>
</div>
</footer>
<script src="~/Scripts/jquery-1.11.0.js"></script>
<script src="~/Scripts/bootstrap-3.1.1-dist/js/bootstrap.min.js"></script>
</body>
</html>
Add a Index view and replace the code with following:
@model MvcSearchFunctionality.Models.PostModel
@{
ViewBag.Title = "Posts";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<div class="row">
<div class="col-md-8">
<h1>Latest
Posts</h1>
@foreach (var item
in Model.Posts)
{
<article>
<h3><a href="blog.html">@item.Title</a></h3>
<div class="row">
<div class="col-sm-12
col-md-12">
<span class="glyphicon
glyphicon-folder-open"></span> <a href="#">@item.Category.Name</a>
<span class="glyphicon
glyphicon-bookmark"></span>
@foreach (var tag in
item.Tags)
{
<a href="#">@tag.Name
</a>
}
</div>
</div>
<br />
<p>@item.Description</p>
<p class="text-right">
<a href="#" class="text-right">continue
reading...
</a>
</p>
<hr />
</article>
}
<ul class="pager">
<li class="previous">
<a href="#">←
Older</a>
</li>
<li class="next">
<a href="#">←
Newer</a>
</li>
</ul>
</div>
<div class="col-md-4">
<div class="panel
panel-default">
<div class="panel-heading">
<h4>Categories</h4>
</div>
<ul class="list-group">
@foreach (var
category in Model.Categories)
{
<li class="list-group-item"><a href="#">@category.Name</a></li>
}
</ul>
</div>
<div class="panel
panel-default">
<div class="panel-heading">
<h4>Tags</h4>
</div>
<div class="panel-body">
<ul class="list-inline">
@foreach (var tag in
Model.Tags)
{
<li><a href="#">@tag.Name</a></li>
}
</ul>
</div>
</div>
</div>
</div>
Do not forget to add a connection string in web.config before
running an application because entity framework finds the connection string
with the name PostDbContext.
<connectionStrings>
<add name="PostDbContext" connectionString="Data
Source=SONY-VAIO\SQLEXPRESS;Initial Catalog=PostDbContext;Integrated Security=true;" providerName="System.Data.SqlClient" />
</connectionStrings>
To learn more about entity framework code first. Refer this link.
Now run an application to test it. It will something look
like below if everyting is ok.
You can ask any question if you find something during
reading or implementation of this post.
Thanks for reading this article. Please can give your
valuable comments and like if you find this helpful for you.
0 comments:
Post a Comment