Get Data From Multiple Tables Using Parameters While Combining LINQ Expressions

Rupesh Kahane
7y
3.6k
0
3

Article

GetDataFromMultipleTablesParameterExpressionInLinq.zip

What is Func<> ?

Func<> is a shorthand for a delegate that takes 0 or more parameters and returns a result. We can assign a Func<> type by providing a lambda expression.

Func<T, TResult> delegate, they are defined separately for each number of parameters from 0 to 17.

public delegate TResult Func<in T, out TResult>(T arg)

What is params?

Parameters are just syntactic sugar. Ultimately, the parameter is just an array. Therefore, the parameter type should be object[] and an expression describing such an array is what you should pass as the second argument.

Here is one simple example of addition of two numbers.

Expression<Func<int, int, int>> expression = (A, B) => A + B;
Func<int, int, int> compiledExpression = expression.Compile();
int result = compiledExpression(3, 4);
Console.WriteLine("Addition is {0}",result); // output 7
Console.ReadLine();

Step 1 Create new MVC Empty project in Visual Studio

Create database with three different tables. Use first table to store user login information; second table to store user details like address and phone number etc.; and third table to store the information of the technology on which the user is currently working.

All three tables having a primary key & foreign key relationship.

Below is the table script which has some dummy data.

USE [Company]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LoginInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[UserName] [varchar](50) NULL,
[Password] [varchar](50) NULL,
CONSTRAINT [PK_LoginInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserDetails](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[Address] [varchar](50) NULL,
CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserPost](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[PostDetails] [varchar](50) NULL,
CONSTRAINT [PK_UserPost] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[LoginInfo] ON
GO
INSERT [dbo].[LoginInfo] ([Id], [FirstName], [UserName], [Password]) VALUES (1, N'Rupesh', N'rupesh', N'123')
GO
INSERT [dbo].[LoginInfo] ([Id], [FirstName], [UserName], [Password]) VALUES (2, N'Ajit', N'ajit', N'123')
GO
SET IDENTITY_INSERT [dbo].[LoginInfo] OFF
GO
SET IDENTITY_INSERT [dbo].[UserDetails] ON
GO
INSERT [dbo].[UserDetails] ([Id], [UserId], [Address]) VALUES (1, 1, N'Baner')
GO
INSERT [dbo].[UserDetails] ([Id], [UserId], [Address]) VALUES (2, 2, N'Viman Nagar')
GO
SET IDENTITY_INSERT [dbo].[UserDetails] OFF
GO
SET IDENTITY_INSERT [dbo].[UserPost] ON
GO
INSERT [dbo].[UserPost] ([Id], [UserId], [PostDetails]) VALUES (1, 1, N'MVC')
GO
INSERT [dbo].[UserPost] ([Id], [UserId], [PostDetails]) VALUES (2, 2, N'Node Js')
GO
SET IDENTITY_INSERT [dbo].[UserPost] OFF
GO
ALTER TABLE [dbo].[UserDetails] WITH CHECK ADD CONSTRAINT [FK_Login] FOREIGN KEY([UserId])
REFERENCES [dbo].[LoginInfo] ([Id])
GO
ALTER TABLE [dbo].[UserDetails] CHECK CONSTRAINT [FK_Login]
GO
ALTER TABLE [dbo].[UserPost] WITH CHECK ADD CONSTRAINT [FK_LoginInfo] FOREIGN KEY([UserId])
REFERENCES [dbo].[LoginInfo] ([Id])
GO
ALTER TABLE [dbo].[UserPost] CHECK CONSTRAINT [FK_LoginInfo]
GO

Step 2

Now, I have added two class libraries in the project - one for Infrastructure & another one for Repository pattern. In Infrastructure class library, I have added .EDMX file generated from database. In Repository class library, I have created Unit of Work & Repository Pattern.

Below code is used for Generic Repository class.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Infrastructure;
using System.Data;
namespace Repository
{
public class GenericRepository<T> : IGenericRepository<T> where T : class
{
internal DbContext context;
internal DbSet<T> dbSet;
public GenericRepository(DbContext context)
{
this.context = context;
this.dbSet = context.Set<T>();
}
public void Save()
{
throw new NotImplementedException();
}
public virtual IEnumerable<T> GetWithRawSql(string query, params object[] parameters)
{
throw new NotImplementedException();
}
public virtual IEnumerable<T> GetAll(
Expression<Func<T, bool>> filter = null,
Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
params Expression<Func<T, object>>[] navigationPropeties)
{
throw new NotImplementedException();
}
public virtual IEnumerable<T> GetAllExpressions(
Expression<Func<T, bool>> filter = null,
Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
params Expression<Func<T, object>>[] naProperties)
{
IQueryable<T> dbQuery = dbSet;
if (filter != null)
{
dbQuery = dbQuery.Where(filter);
}
foreach (Expression<Func<T, object>> nProperty in naProperties)
dbQuery = dbQuery.Include<T, object>(nProperty);
if (orderBy != null)
{
dbQuery = orderBy(dbQuery);
}
return dbQuery.ToList();
}
public virtual T GetByID(object id)
{
throw new NotImplementedException();
}
public virtual void Insert(T entity)
{
throw new NotImplementedException();
}
public virtual void Delete(object id)
{
throw new NotImplementedException();
}
public virtual void Delete(T entityToDelete)
{
throw new NotImplementedException();
}
public virtual void Update(T entityToUpdate)
{
throw new NotImplementedException();
}
public virtual T GetSingle(Expression<Func<T, bool>> where,
params Expression<Func<T, object>>[] navigationProperties)
{
throw new NotImplementedException();
}
private IQueryable<T> orderBy(IQueryable<T> dbQuery)
{
throw new NotImplementedException();
}
}
}

The following code is used for IGeneric Repository class.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Entity;
namespace Repository
{
public interface IGenericRepository<T> where T : class
{
IEnumerable<T> GetAll(Expression<Func<T, bool>> filter = null,
Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
params Expression<Func<T, object>>[] navigationPropeties);
IEnumerable<T> GetAllExpressions(
Expression<Func<T, bool>> filter = null,
Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
params Expression<Func<T, object>>[] naProperties);
IEnumerable<T> GetWithRawSql(string query, params object[] parameters);
T GetByID(object id);
void Insert(T entity);
void Delete(object id);
void Delete(T entityToDelete);
void Update(T entityToUpdate);
void Save();
}
}

The below code is for Unit of Work Pattern.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Infrastructure;
namespace Repository
{
public class UnitOfWork : IDisposable
{
private CompanyEntities context = new CompanyEntities();
private IGenericRepository<LoginInfo> loginRepository;
private IGenericRepository<UserDetail> userDetailsRepository;
private IGenericRepository<UserPost> userPostRepository;
public IGenericRepository<LoginInfo> LoginRepository
{
get
{
return loginRepository ?? (loginRepository = new GenericRepository<LoginInfo>(context));
}
}
public IGenericRepository<UserDetail> UserDetailsRepository
{
get
{
return userDetailsRepository ?? (userDetailsRepository = new GenericRepository<UserDetail>(context));
}
}
public IGenericRepository<UserPost> UserPostRepository
{
get
{
return userPostRepository ?? (userPostRepository = new GenericRepository<UserPost>(context));
}
}
private bool disposed = false;
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
context.Dispose();
}
}
this.disposed = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
}
}

Step 3

Now, create one folder, ViewModel, in your project. Add HomeViewModel and some properties into that. I would like to display First Name, Address, Technology on View, so I am going to add these properties as below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Infrastructure;
namespace Core.ViewModel
{
public class HomeViewModel
{
public HomeViewModel(UserPost objUserPost)
{
FirstName = objUserPost.LoginInfo.FirstName;
Address = objUserPost.LoginInfo.UserDetails.FirstOrDefault().Address;
PostDetails = objUserPost.PostDetails;
}
public string FirstName { get; set; }
public string Address { get; set; }
public string PostDetails { get; set; }
}
}

Step 4

Now, create Base Controller to access the global object of Unit Of Work by declaring constructor.

using Repository;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace Core.Controllers
{
public class BaseController : Controller
{
protected UnitOfWork UnitoffWork { get; private set; }
public BaseController()
{
UnitoffWork = new UnitOfWork();
}
}
}

Step 5

Now, add HomeController. In Action method by declaring Expression, we can get information from multiple tables into a single variable.

Note - As we have a relationship in between these three tables, we will get records based on that relationship.

using Infrastructure;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using System.Web.Mvc;
namespace Core.Controllers
{
public class HomeController : BaseController
{
public ActionResult Index()
{
Expression<Func<UserPost, object>> parameter1 = v => v.LoginInfo;
Expression<Func<UserPost, object>> parameter2 = v => v.LoginInfo.UserDetails;
Expression<Func<UserPost, object>>[] parameterArray = new Expression<Func<UserPost, object>>[] { parameter1, parameter2 };
var userPost = UnitoffWork.UserPostRepository.GetAllExpressions(naProperties: parameterArray).Select(u => new HomeViewModel(u)).ToList();
return View(userPost);
}
}
}

Step 6

Now, create View against above action method.

@model IEnumerable<Core.ViewModel.HomeViewModel>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Parameter Array</title>
</head>
<body style="margin-left:50px">
<h2>
Get data from mutliple tables using parameters when combining linq expressions
</h2>
@foreach (var item in Model)
{
@Html.Label("Name") : @Html.Label(item.FirstName.ToString())
@Html.Label("Address") : @Html.Label(item.Address.ToString())
@Html.Label("Working On") : @Html.Label(item.PostDetails.ToString())
}
</body>
</html>

Step 7

Now, run the application and you will see the result.

Summary

In this article, you learned the basics of how to get data from multiple tables using parameters when combining LINQ Expressions, using repository pattern.

Up Next

Ebook Download

View all

ASP.NET MVC 5: A Beginner’s Guide

Read by 10.8k people

Download Now!

Learn

View all