LINQ with an MVC Project

by

This blog contains basic information about LINQ. Also, we will see how can we use LINQ in an MVC project.

What is LINQ?

LINQ stands for Language Integrated Query. LINQ can be used to query from a variety of data sources for eg  SQL Server, Array, and XML. LINQ defines keywords that you can use to select, filter, sort, group, and transform data.

Advantages and Disadvantages of using LINQ

Advantages – LINQ in C# offers intellisense which makes the developer’s life easy. LINQ is type-safe. We can debug LINQ queries in Visual Studio like normal C# statements. We can resolve compile time errors in LINQ. We can use the Lambda expression to write LINQ queries.

Disadvantages – LINQ is not recommended for hug applications due to performance issues. If there is a change in LINQ then the application needs to be recompiled and redeployed. Compare to SQL stored procedures, LINQ is not precompiled while stored procedures in SQL are precompiled, so stored procedures perform faster than LINQ.

LINQ Syntax

from <alias> in <collection> | [Clauses] select <alias>

Let’s see the use of LINQ in the below code.

    class Program
    {
        static void Main(string[] args)
        {
            List<int> numberList = new List<int>();
            numberList.Add(13);
            numberList.Add(15);
            numberList.Add(12);
            numberList.Add(19);
            numberList.Add(20);
           
            //LINQ to get even number
            var evenResult = from a in numberList where a % 2 == 0 select a;
            //LINQ to get odd number
            var oddResult = from a in numberList where a % 2 != 0 select a;
            foreach (int x in evenResult)
            {
                Console.WriteLine(x + ” is even number”);
            }
            foreach (int y in oddResult)
            {
                Console.WriteLine(y + ” is odd number”);
            }
        }
    }

Create 3 tables in MS-SQL –

1. Review_Category – It stores the Category of the product.

/****** Object:  Table [dbo].[Review_Category]    Script Date: 9/19/2018 11:20:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Review_Category](
       [catid] [int] IDENTITY(1,1) NOT NULL,
       [category] [varchar](150) NULL,
       [subcategory] [varchar](150) NULL,
       [status] [bit] NULL,
 CONSTRAINT [PK_Review_Category] PRIMARY KEY CLUSTERED
(
       [catid] 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

Data Available in this table –

2. Review_Users – It stores Users data

/****** Object:  Table [dbo].[Review_Users]    Script Date: 9/19/2018 11:21:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Review_Users](
       [UserId] [bigint] IDENTITY(1,1) NOT NULL,
       [uid] [uniqueidentifier] NULL,
       [Name] [varchar](250) NULL,
       [Email] [varchar](250) NULL,
       [Password] [varchar](150) NULL,
       [Mobile] [varchar](50) NULL,
       [City] [varchar](50) NULL,
       [State] [varchar](50) NULL,
       [Country] [varchar](50) NULL,
       [Pincode] [varchar](50) NULL,
       [Status] [bit] NULL,
       [Creationdate] [datetime] NULL,
       [Socialmedia] [varchar](10) NULL,
 CONSTRAINT [PK_Review_Users] PRIMARY KEY CLUSTERED
(
       [UserId] 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

Data available in this table –

3. Review_UsersReview – Which stores review given by users.

/****** Object:  Table [dbo].[Review_UsersReview]    Script Date: 9/19/2018 11:22:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Review_UsersReview](
       [reviewid] [bigint] IDENTITY(1,1) NOT NULL,
       [uid] [uniqueidentifier] NULL,
       [reviewfor] [nvarchar](350) NULL,
       [reviewtitle] [nvarchar](500) NULL,
       [productid] [bigint] NULL,
       [catid] [int] NULL,
       [purchasemonth] [int] NULL,
       [purchaseyear] [int] NULL,
       [purchasemode] [varchar](50) NULL,
       [purchasecondition] [varchar](50) NULL,
       [pros] [nvarchar](500) NULL,
       [cons] [nvarchar](500) NULL,
       [feedback] [nvarchar](500) NULL,
       [productimage] [varchar](350) NULL,
       [productrating] [decimal](18, 0) NULL,
       [reviewdate] [datetime] NULL,
       [reviewstatus] [bit] NULL,
       [reviewwithbill] [bit] NULL,
 CONSTRAINT [PK_Review_UsersReview] PRIMARY KEY CLUSTERED
(
       [reviewid] 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

Data available in this table –

Create a new MVC project.
Add models in your project.

UsersModel

public class UsersModel
    {
        public long UserId { get; set; }
        [Required(ErrorMessage = “Username Required”, AllowEmptyStrings = false)]
        public string Email { get; set; }
        [Required(ErrorMessage = “Username Required”, AllowEmptyStrings = false)]
        public string Mobile { get; set; }
        [Required(ErrorMessage = “Password Required”, AllowEmptyStrings = false)]
        [DataType(DataType.Password)]
        public string Password { get; set; }
        public bool RememberMe { get; set; }
        [Required]
        public string Name { get; set; }
        public string Country { get; set; }
        public string Profession { get; set; }
        public Guid Uid { get; set; }
    }

ReviewModel

public class ReviewModel
    {
        [Required]
        [Display(Name = “Product or Service”)]
        public string ReviewFor { get; set; }
        [Required]
        [Display(Name = “Review Title”)]
        public string ReviewTitle { get; set; }
        [Required]
        [Display(Name = “Product Category”)]
        public string ProductCategory { get; set; }
        public string ProductSubCategory { get; set; }
        [Required]
        [Display(Name = “Product/Service Brand”)]
        public string ProductBrand { get; set; }
        [Required]
        [Display(Name = “Product Model”)]
        public string ProductModel { get; set; }
        [Required]
        [Display(Name = “Purchase Mode”)]
        public string PurchaseMode { get; set; }
        [Required]
        [Display(Name = “Purchase Month”)]
        public int? PurchaseMonth { get; set; }
        [Required]
        [RegularExpression(@”^(d{4})$”, ErrorMessage = “Enter a valid 4 digit Year”)]
        [Display(Name = “Year”)]
        public int PurchaseYear { get; set; }
        public string Condition { get; set; }
        [Required]
        [Display(Name = “Feedback”)]
        public string AnyComment { get; set; }
        public string ProductImage { get; set; }
        public decimal OverAllRating { get; set; }
        public Nullable<decimal> ProductRating { get; set; }
        public string PositiveSide { get; set; }
        public string NegativeSide { get; set; }
        public bool ReviewWithBill { get; set; }
        public List<SelectListItem> Categories { get; set; }
        public int? Catid { get; set; }
        public string CategoryName { get; set; }
    }

UserReviewModel

public class UserReviewModel
    {
        public ReviewModel ReviewViewModel { get; set; }
        public UsersModel UserViewModel { get; set; }
    }

Add Entity Data Model in your project.

Your Model.edmx file look like this.

Let’s play with data using LINQ

1. LINQ to login a user by checking credentials

Review_Users LoginStatus(UsersModel userModel)
        {
            using (ProductRatingTestEntities reviewContext = new ProductRatingTestEntities())
            {
                var user = reviewContext.Review_Users.Where(u => (u.Email.Equals(userModel.Email) || u.Mobile.Equals(userModel.Mobile))
                && u.Password.Equals(userModel.Password)).FirstOrDefault();
                return user;
            }
        }

2. Check User by email or mobile

public Review_Users CheckUser(UsersModel userModel)
        {
            using (ProductRatingTestEntities reviewContext = new ProductRatingTestEntities())
            {
                var user = reviewContext.Review_Users.Where(u => u.Email.Equals(userModel.Email) || u.Mobile.Equals(userModel.Mobile)).FirstOrDefault();
                return user;
            }
        }

3. Register a user in database

        public bool SignupProcess(UsersModel userModel)
        {
            try
            {
                using (ProductRatingTestEntities reviewContext = new ProductRatingTestEntities())
                {
                    var users = new Review_Users()
                    {
                        Email = userModel.Email,
                        Name = userModel.Name,
                        Mobile = userModel.Mobile,
                        Password = userModel.Password,
                        Country = userModel.Country,
                        Creationdate = DateTime.Now,
                        Status = false,
                        uid = Guid.NewGuid()
                    };
                    reviewContext.Review_Users.Add(users);
                    reviewContext.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

4. Get latest reviews posted by users group by product category.

Index.cshtml (View to bind model data to display on page)

@model IEnumerable<MvcApplication1.Models.UserReviewModel>
@{
    ViewBag.Title = “Index”;
}
<h2>Index</h2>
@foreach (var item in Model)
{
    @item.UserViewModel.Name <br />
    @item.ReviewViewModel.ReviewTitle <br />
    <a href=”@item.ReviewViewModel.ReviewFor-review”>Read more on @item.ReviewViewModel.ReviewFor Review(s)</a><br />
    @item.ReviewViewModel.CategoryName <span>-></span>  @item.ReviewViewModel.ProductSubCategory
    <hr />
}

Index action method in HomeController

public ActionResult Index()
        {
            List<ReviewModel> reviews = new List<ReviewModel>();
            DataAccessLayer objDAL = new DataAccessLayer();
            List<UserReviewModel> objRevModel = new List<UserReviewModel>();
            objRevModel = objDAL.GetReviewData().AsEnumerable().ToList();
            return View(objRevModel);
        }
        public IQueryable<UserReviewModel> GetReviewData()
        {
            ProductRatingTestEntities objEnt = new ProductRatingTestEntities();
            IQueryable<UserReviewModel> iQuery = null;
            iQuery = (from u in objEnt.Review_Users
                      join
                          r in objEnt.Review_UsersReview
                          on u.uid equals r.uid
                      join s in objEnt.Review_Category on r.catid equals s.catid
                      select new UserReviewModel
                      {
                          ReviewViewModel = new ReviewModel { AnyComment = r.feedback, ReviewTitle = r.reviewtitle, CategoryName = s.category, ProductSubCategory = s.subcategory, ReviewFor = r.reviewfor.Replace(” “, “-“).Trim() },
                          UserViewModel = new UsersModel { Name = u.Name, Email = u.Email },
                      }).GroupBy(s => new { s.ReviewViewModel.ReviewFor }).Select(g => g.FirstOrDefault());
            return iQuery;
        }

5. Get specific Product Review

public IQueryable<UserReviewModel> GetSpecificProductReview(string product)
        {
            ProductRatingTestEntities objEnt = new ProductRatingTestEntities();
            IQueryable<UserReviewModel> iQuery = null;
            IQueryable<Review_Category> iQ = null;
            iQ = objEnt.Review_Category;
            iQuery = (from u in objEnt.Review_Users
                      join
                          r in objEnt.Review_UsersReview
                          on u.uid equals r.uid
                      join s in objEnt.Review_Category on r.catid equals s.catid
                      where r.reviewfor == product
                      select new UserReviewModel
                      {
                          ReviewViewModel = new ReviewModel { AnyComment = r.feedback, ReviewTitle = r.reviewtitle, CategoryName = s.category, ProductSubCategory = s.subcategory, ReviewFor = r.reviewfor.Replace(” “, “-“).Trim() },
                          UserViewModel = new UsersModel { Name = u.Name, Email = u.Email },
                      });
            int count = iQuery.Count();
            return iQuery;
        }

6. Get Count by Category

public void CountData()
        {
            ProductRatingTestEntities objEnt = new ProductRatingTestEntities();
            List<ReviewModel> reviews = new List<ReviewModel>();
            var catCount = from p in objEnt.Review_UsersReview
                           group p by p.catid into g
                           select new { Categaory = g.Key, Count = g.Count() };
        }

You may like other blogs –

MVC Tutorial
Web API Tutorial
Is Angular JS different from Angular?

Interview Questions and Answers Series –

MVC Interview Questions and Answers
Web API interview questions and answers

No related posts.

Leave a Comment

RSS
Facebook
Twitter
YouTube
YouTube
Instagram