ASP.NET

Get Data From Multiple Tables Using Parameters While Combining LINQ Expressions

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.

  1. 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.
  1. Expression<Func<int, int, int>> expression = (A, B) => A + B;  
  2. Func<int, int, int> compiledExpression = expression.Compile();  
  3. int result = compiledExpression(3, 4);  
  4. Console.WriteLine("Addition is {0}",result);  // output 7  
  5. 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.
  1. USE [Company]  
  2. GO  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7. SET ANSI_PADDING ON  
  8. GO  
  9. CREATE TABLE [dbo].[LoginInfo](  
  10.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  11.     [FirstName] [varchar](50) NULL,  
  12.     [UserName] [varchar](50) NULL,  
  13.     [Password] [varchar](50) NULL,  
  14.  CONSTRAINT [PK_LoginInfo] PRIMARY KEY CLUSTERED   
  15. (  
  16.     [Id] ASC  
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  18. ) ON [PRIMARY]  
  19.   
  20. GO  
  21. SET ANSI_PADDING OFF  
  22. GO  
  23. SET ANSI_NULLS ON  
  24. GO  
  25. SET QUOTED_IDENTIFIER ON  
  26. GO  
  27. SET ANSI_PADDING ON  
  28. GO  
  29. CREATE TABLE [dbo].[UserDetails](  
  30.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  31.     [UserId] [int] NOT NULL,  
  32.     [Address] [varchar](50) NULL,  
  33.  CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED   
  34. (  
  35.     [Id] ASC  
  36. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  37. ) ON [PRIMARY]  
  38.   
  39. GO  
  40. SET ANSI_PADDING OFF  
  41. GO  
  42. SET ANSI_NULLS ON  
  43. GO  
  44. SET QUOTED_IDENTIFIER ON  
  45. GO  
  46. SET ANSI_PADDING ON  
  47. GO  
  48. CREATE TABLE [dbo].[UserPost](  
  49.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  50.     [UserId] [int] NOT NULL,  
  51.     [PostDetails] [varchar](50) NULL,  
  52.  CONSTRAINT [PK_UserPost] PRIMARY KEY CLUSTERED   
  53. (  
  54.     [Id] ASC  
  55. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  56. ) ON [PRIMARY]  
  57.   
  58. GO  
  59. SET ANSI_PADDING OFF  
  60. GO  
  61. SET IDENTITY_INSERT [dbo].[LoginInfo] ON   
  62.   
  63. GO  
  64. INSERT [dbo].[LoginInfo] ([Id], [FirstName], [UserName], [Password]) VALUES (1, N'Rupesh', N'rupesh', N'123')  
  65. GO  
  66. INSERT [dbo].[LoginInfo] ([Id], [FirstName], [UserName], [Password]) VALUES (2, N'Ajit', N'ajit', N'123')  
  67. GO  
  68. SET IDENTITY_INSERT [dbo].[LoginInfo] OFF  
  69. GO  
  70. SET IDENTITY_INSERT [dbo].[UserDetails] ON   
  71.   
  72. GO  
  73. INSERT [dbo].[UserDetails] ([Id], [UserId], [Address]) VALUES (1, 1, N'Baner')  
  74. GO  
  75. INSERT [dbo].[UserDetails] ([Id], [UserId], [Address]) VALUES (2, 2, N'Viman Nagar')  
  76. GO  
  77. SET IDENTITY_INSERT [dbo].[UserDetails] OFF  
  78. GO  
  79. SET IDENTITY_INSERT [dbo].[UserPost] ON   
  80.   
  81. GO  
  82. INSERT [dbo].[UserPost] ([Id], [UserId], [PostDetails]) VALUES (1, 1, N'MVC')  
  83. GO  
  84. INSERT [dbo].[UserPost] ([Id], [UserId], [PostDetails]) VALUES (2, 2, N'Node Js')  
  85. GO  
  86. SET IDENTITY_INSERT [dbo].[UserPost] OFF  
  87. GO  
  88. ALTER TABLE [dbo].[UserDetails]  WITH CHECK ADD  CONSTRAINT [FK_Login] FOREIGN KEY([UserId])  
  89. REFERENCES [dbo].[LoginInfo] ([Id])  
  90. GO  
  91. ALTER TABLE [dbo].[UserDetails] CHECK CONSTRAINT [FK_Login]  
  92. GO  
  93. ALTER TABLE [dbo].[UserPost]  WITH CHECK ADD  CONSTRAINT [FK_LoginInfo] FOREIGN KEY([UserId])  
  94. REFERENCES [dbo].[LoginInfo] ([Id])  
  95. GO  
  96. ALTER TABLE [dbo].[UserPost] CHECK CONSTRAINT [FK_LoginInfo]  
  97. 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.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data.Entity;  
  4. using System.Linq;  
  5. using System.Linq.Expressions;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8. using Infrastructure;   
  9. using System.Data;   
  10.   
  11. namespace Repository  
  12. {  
  13.     public class GenericRepository<T> : IGenericRepository<T> where T : class  
  14.     {  
  15.         internal DbContext context;  
  16.         internal DbSet<T> dbSet;  
  17.   
  18.         public GenericRepository(DbContext context)  
  19.         {  
  20.             this.context = context;  
  21.             this.dbSet = context.Set<T>();  
  22.         }  
  23.   
  24.         public void Save()  
  25.         {  
  26.             throw new NotImplementedException();  
  27.         }  
  28.   
  29.         public virtual IEnumerable<T> GetWithRawSql(string query, params object[] parameters)  
  30.         {  
  31.             throw new NotImplementedException();  
  32.         }  
  33.   
  34.         public virtual IEnumerable<T> GetAll(  
  35.             Expression<Func<T, bool>> filter = null,  
  36.             Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,  
  37.             params Expression<Func<T, object>>[] navigationPropeties)  
  38.         {  
  39.             throw new NotImplementedException();  
  40.         }  
  41.   
  42.         public virtual IEnumerable<T> GetAllExpressions(  
  43.             Expression<Func<T, bool>> filter = null,  
  44.             Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,  
  45.             params Expression<Func<T, object>>[] naProperties)  
  46.         {  
  47.             IQueryable<T> dbQuery = dbSet;  
  48.   
  49.             if (filter != null)  
  50.             {  
  51.                 dbQuery = dbQuery.Where(filter);  
  52.             }  
  53.   
  54.             foreach (Expression<Func<T, object>> nProperty in naProperties)  
  55.                 dbQuery = dbQuery.Include<T, object>(nProperty);  
  56.   
  57.             if (orderBy != null)  
  58.             {  
  59.                 dbQuery = orderBy(dbQuery);  
  60.             }  
  61.   
  62.             return dbQuery.ToList();  
  63.         }  
  64.   
  65.         public virtual T GetByID(object id)  
  66.         {  
  67.             throw new NotImplementedException();  
  68.         }  
  69.   
  70.         public virtual void Insert(T entity)  
  71.         {  
  72.             throw new NotImplementedException();  
  73.         }  
  74.   
  75.         public virtual void Delete(object id)  
  76.         {  
  77.             throw new NotImplementedException();  
  78.         }  
  79.   
  80.         public virtual void Delete(T entityToDelete)  
  81.         {  
  82.             throw new NotImplementedException();  
  83.         }  
  84.   
  85.         public virtual void Update(T entityToUpdate)  
  86.         {  
  87.             throw new NotImplementedException();  
  88.         }  
  89.   
  90.         public virtual T GetSingle(Expression<Func<T, bool>> where,  
  91.            params Expression<Func<T, object>>[] navigationProperties)  
  92.         {  
  93.             throw new NotImplementedException();  
  94.         }  
  95.   
  96.         private IQueryable<T> orderBy(IQueryable<T> dbQuery)  
  97.         {  
  98.             throw new NotImplementedException();  
  99.         }  
  100.   
  101.     }  
  102. }  
The following code is used for IGeneric Repository class.
  1. using System;  
  2. using System.Collections;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Linq.Expressions;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8. using System.Data;   
  9. using System.Data.Entity;   
  10.   
  11. namespace Repository  
  12. {  
  13.     public interface IGenericRepository<T> where T : class  
  14.     {  
  15.         IEnumerable<T> GetAll(Expression<Func<T, bool>> filter = null,  
  16.             Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,  
  17.             params Expression<Func<T, object>>[] navigationPropeties);  
  18.   
  19.         IEnumerable<T> GetAllExpressions(  
  20.           Expression<Func<T, bool>> filter = null,  
  21.           Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,  
  22.           params Expression<Func<T, object>>[] naProperties);  
  23.   
  24.         IEnumerable<T> GetWithRawSql(string query, params object[] parameters);  
  25.   
  26.         T GetByID(object id);  
  27.   
  28.         void Insert(T entity);  
  29.   
  30.         void Delete(object id);  
  31.   
  32.         void Delete(T entityToDelete);  
  33.   
  34.         void Update(T entityToUpdate);  
  35.   
  36.         void Save();  
  37.     }  
  38. }  
The below code is for Unit of Work Pattern.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using Infrastructure;  
  7.   
  8. namespace Repository  
  9. {  
  10.     public class UnitOfWork : IDisposable  
  11.     {  
  12.         private CompanyEntities context = new CompanyEntities();  
  13.   
  14.         private IGenericRepository<LoginInfo> loginRepository;  
  15.         private IGenericRepository<UserDetail> userDetailsRepository;  
  16.         private IGenericRepository<UserPost> userPostRepository;  
  17.         public IGenericRepository<LoginInfo> LoginRepository  
  18.         {  
  19.             get  
  20.             {  
  21.                 return loginRepository ?? (loginRepository = new GenericRepository<LoginInfo>(context));  
  22.             }  
  23.         }  
  24.         public IGenericRepository<UserDetail> UserDetailsRepository  
  25.         {  
  26.             get  
  27.             {  
  28.                 return userDetailsRepository ?? (userDetailsRepository = new GenericRepository<UserDetail>(context));  
  29.             }  
  30.         }  
  31.         public IGenericRepository<UserPost> UserPostRepository  
  32.         {  
  33.             get  
  34.             {  
  35.                 return userPostRepository ?? (userPostRepository = new GenericRepository<UserPost>(context));  
  36.             }  
  37.         }  
  38.           
  39.         private bool disposed = false;  
  40.         protected virtual void Dispose(bool disposing)  
  41.         {  
  42.             if (!this.disposed)  
  43.             {  
  44.                 if (disposing)  
  45.                 {  
  46.                     context.Dispose();  
  47.                 }  
  48.             }  
  49.             this.disposed = true;  
  50.         }  
  51.   
  52.         public void Dispose()  
  53.         {  
  54.             Dispose(true);  
  55.             GC.SuppressFinalize(this);  
  56.         }  
  57.     }  
  58. }  
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.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using Infrastructure;  
  6.   
  7. namespace Core.ViewModel  
  8. {  
  9.     public class HomeViewModel  
  10.     {  
  11.         public HomeViewModel(UserPost objUserPost)  
  12.         {  
  13.             FirstName = objUserPost.LoginInfo.FirstName;  
  14.             Address = objUserPost.LoginInfo.UserDetails.FirstOrDefault().Address;  
  15.             PostDetails = objUserPost.PostDetails;  
  16.         }  
  17.   
  18.         public string FirstName { get; set; }  
  19.         public string Address { get; set; }  
  20.         public string PostDetails { get; set; }  
  21.     }  
  22. }  
Step 4

Now, create Base Controller to access the global object of Unit Of Work by declaring constructor.
  1. using Repository;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Web;  
  6. using System.Web.Mvc;  
  7.   
  8. namespace Core.Controllers  
  9. {  
  10.     public class BaseController : Controller  
  11.     {  
  12.         protected UnitOfWork UnitoffWork { get; private set; }  
  13.         public BaseController()  
  14.         {  
  15.             UnitoffWork = new UnitOfWork();  
  16.         }  
  17.     }  
  18. }  
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.
  1. using Infrastructure;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Linq.Expressions;  
  6. using System.Web;  
  7. using System.Web.Mvc;  
  8.   
  9. namespace Core.Controllers  
  10. {  
  11.     public class HomeController : BaseController  
  12.     {  
  13.         public ActionResult Index()  
  14.         {  
  15.             Expression<Func<UserPost, object>> parameter1 = v => v.LoginInfo;  
  16.             Expression<Func<UserPost, object>> parameter2 = v => v.LoginInfo.UserDetails;  
  17.   
  18.             Expression<Func<UserPost, object>>[] parameterArray = new Expression<Func<UserPost, object>>[] { parameter1, parameter2 };  
  19.             var userPost = UnitoffWork.UserPostRepository.GetAllExpressions(naProperties: parameterArray).Select(u => new HomeViewModel(u)).ToList();  
  20.             return View(userPost);  
  21.         }  
  22.     }  
  23. }   
Step 6

Now, create View against above action method.
  1. @model IEnumerable<Core.ViewModel.HomeViewModel>  
  2.   
  3. @{  
  4.     Layout = null;  
  5. }  
  6.   
  7. <!DOCTYPE html>  
  8.   
  9. <html>  
  10. <head>  
  11.     <meta name="viewport" content="width=device-width" />  
  12.     <title>Parameter Array</title>  
  13. </head>  
  14. <body style="margin-left:50px">  
  15.     <h2>  
  16.          Get data from mutliple tables using parameters when combining linq expressions  
  17.     </h2>  
  18.    @foreach (var item in Model)  
  19.    {  
  20.        <br />  
  21.     <span style="color:red"> @Html.Label("Name") :</span>  @Html.Label(item.FirstName.ToString())  
  22.        <br />  
  23.      <span style="color:red"> @Html.Label("Address")  :</span>  @Html.Label(item.Address.ToString())  
  24.        <br />  
  25.       <span style="color:red"> @Html.Label("Working On") :</span>  @Html.Label(item.PostDetails.ToString())  
  26.        <br />         
  27.    }  
  28. </body>  
  29. </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
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs