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:
Download Bootstrap compiled
and minified.
Download Jquey compressed.
<!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.
3 comments:
Very helpful... Thanks!
Thanks a lots for this tutorial. Could please update it with all the CRUD operations.
Great Article
IEEE Projects for Engineering Students
Final Year Projects for CSE
Post a Comment