LINQ with an MVC Project

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, 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 developer's life easy. LINQ is type safe. We can debug LINQ query in Visual Studio like normal C# statement. We can resolve compile time error in LINQ. We can use Lambda expression to write LINQ queries.

Disadvantages - LINQ is not recommended for hug applications due to performance issue. If there is a change in LINQ then application needs to be recompiled and redeployed. Compare to SQL stored procedure, 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 use of LINQ in 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 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

Comments

Popular