Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

IN Sub query with LINQ

LINQ Self-contained multi valued sub query

Introduction 

My collogue told me LINQ is created to achieve SQL queries in programming language like(C#,VB), my quick reply was “Tell me linq query that achieve linq subquery logic using IN? ”.

Simply my question was convert following SQL query (Self-contained multi valued sub query) into linq query,
SELECT * FROM  Products WHERE
CategoryID in (SELECT CategoryID FROM Categories WHERE
CategoryName='Mobile') 

After some debate he started applying some logic like,

   var CellPhones=  from p in ListProducts
    where p.CategoryId =
            (
                        from c in categories
                        where c.CategoryName == "Mobile"
                        select c.Id
 
            )
    select p;


But this logic was not working (Syntax error), in last his conclusion was we cannot achieve all SQL syntax and logics with LINQ.

What is LINQ? Why LINQ?

These are set features introduced by Microsoft that extends powerful query capabilities to the language syntax of C# and VB.

We can achieve query capabilities with

1. LINQ to SQL

2. LINQ to Dataset

3. LINQ to objects

4. LINQ to XML

5. LINQ to entities.


SQL subquery with LINQ 

SQL subquery:

Select Query inside another query is a sub query, Select statement embedded in DML statement or nested in outer query.

SQL Self-contained multi valued sub query:

Self-contained multi-valued sub query still return a single column but it may produce multiple values for the column.

This multi valued sub query used with IN Predicate and this returns multiple values according to match (true or false).

Sub Query with LINQ:

If we want to get the equivalent of SQL subquery with IN (Self-contained multi valued sub query), you need to construct an inner query first, and then use the Contains () method in outer query or main query.

Following example is multi valued subquery that returns single column but it produce multiple values for the column category id.

LINQ Subquery Example:

//Generic product list data
List<Product> ListProducts = ProductDataAccess.GetProducts();
//Generic Categories list data
List<ProductCategory> categories = ProductDataAccess.GetProductCategories();

 

//Returns Records (products) that satisfy where condition Categoryname='Mobile'
var innerQuery = from c in categories where
c.CategoryName =="Mobile" select c.Id;
var OuterQuery = from p in ListProducts where innerQuery.Contains(p.CategoryId) select p;

 

 

 

 

 

Correlated subquery in Linq

In this article I will explain with sample example how you can achieve sql correlated subquery logic using linq in C#.

Sql Correlated Sub queries:

Sub query depends on the outer query for its values. This means that the sub query is executed repeatedly, once for each row that might be selected by the outer query.

   Like join outer query one or more columns matches with sub query one or more columns, and sub query returns matching records.

Linq Correlated Sub Queries:

With linq query you can achieve correlated functionality using where condition with two linq queries.  

Example:

In below example Outer linq query CategoryId is compared with inner linq(2nd linq query )CategoryId.

 

Linq Inner qury return categoryid if categoryName matches where condition and linq inner query cetagoryid matches with outer query.

 

Finally linq nner query returned categoryid value is used in outer select statement.


Products and Categories Data:


//Generic product list data
 List<Product> ListProducts = ProductDataAccess.GetProducts();
//Generic Categories list data
List<ProductCategory> categories = ProductDataAccess.GetProductCategories();

       

Linq Correlated subquery:


//Returns only Records (products) that satisfy where condition Categoryname='Mobile' 
 var CellPhones = (from item in ListProducts
                      where item.ProductName != string.Empty
                      where item.CategoryId == (from subitem in categories
                      where subitem.CategoryName.Trim() == "Mobile"                                    select subitem.Id).First()
                       select item).ToList();

Writing Unit tests in C# with moq

Writing Unit testing in C# with moq

What is Unit testing?

Wikipedia definition
Unit testing is a software testing method by which individual units of source code, sets of one or more computer program modules together with associated control data, usage procedures, and operating procedures, are tested to determine whether they are fit for use.

Reference: https://en.wikipedia.org/wiki/Unit_testing

What is Moq?

Moq is the mocking library for .NET developed that take full advantage of .NET 3.5 (i.e. Linq expression trees) and C# 3.0 features (i.e. lambda expressions) that make it the most productive, type-safe and refactoring-friendly mocking library available. And it supports mocking interfaces as well as classes.

Installing Moq:

You can download Moq from GitHub and add the appropriate references to your project, or you can install it using nugget package manager.



Unit testing in VS 2013 with Moq:

I want to test validation business logic for my finance web application following business entities I am using for product data.

  public class Product
   {
      public int ProductId { get; set; }
 
      public string ProductName { get; set; }
 
      public decimal Price { get; set; }
 
      public ProductCategory Category { get; set; }
 
      public string ImageUrl { get; set; }
   }

Product data validation logic and used interfaces for notication:

namespace C4RandDWebApp
{
    public class ProductDataValidation
    {
        private readonly ILogger _logger;
        private readonly IEmailer _emailer;
 
        public ProductDataValidation(ILogger Logger, IEmailer Emailer)
        {
            _logger = Logger;
            _emailer = Emailer;
        }
 
 
        public bool ValidateProductame(Product Product)
        {
            if (string.IsNullOrEmpty(Product.ProductName))
                return false;
           {
                     _logger.LogWarning("Invalid username" + Product.ProductName);
                    // Email internet dev
                    _emailer.SendWarningEmail(Product.ProductName,
                        "Invalid username");
          }
            return true;
        }

        public bool ValidateProductPrice(Product Product)
        {
            if (Product.Price == null || Product.Price == 0M)
                return false;
            return true;
        }

        public bool ValidateProductCategory(Product Product)
        {
            if (string.IsNullOrEmpty(Product.Category.CategoryName))
                return false;
            return true;
        }
    }
}
 


Created unit testing project named C4Testing and added  all required dll reference to project. 

Created following test class inside project that unit tests all scenarios for my application product data validation. 

Unit testing Class with Moq:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Moq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using C4RandDWebApp;
 
namespace C4Testing
{
    /// <summary>
    ///This is a test class for Product validation and is intended
    ///to contain all Product validation Unit Tests
    ///</summary>
    [TestClass]
    class ProductDataValidationTests
    {
        // Mocks
        private Mock<ILogger> _mockLogger;
        private Mock<IEmailer> _mockEmailer;
 
       //Declaration
        private IProductDataValidation _ProductValidation;
 
 
        //initalize
        [TestInitialize]
        public void ProductValiatotInitialise()
        {
            _mockLogger = new Mock<ILogger>();
            _mockEmailer = new Mock<IEmailer>();
            _ProductValidation = new C4RandDWebApp.ProductDataValidation(_mockLogger.Object, _mockEmailer.Object);
 
        }
 
 
        #region Sad Path
 
        /// <summary>
        /// Validates Product name. 
        /// Result is invalid Product Name.
        /// </summary>
        [TestMethod]
        public void ChaekEmptyProductName()
        {
            var target = _ProductValidation;
            var product = new Product()
            {
                ProductName = "",
                Price = 115,
                Category = new ProductCategory()
              {
                  CategoryName = "Book"
              },
                ImageUrl = @"C:/data"
 
            };
            Assert.IsFalse(target.ValidateProductame(product));
 
        }
 
        #endregion
 
 
        #region Happy Path
        /// <summary>
        /// validates Product name. 
        /// Result is valid Product Name.
        /// </summary>
        [TestMethod]
        public void ChaekValidProductName()
        {
            var target = _ProductValidation;
            var product = new Product()
            {
                ProductName = "CodeChef4u SQL rcipies",
                Price = 500,
                Category = new ProductCategory()
                {
                    CategoryName = "Book"
                },
                ImageUrl = @"C:/data"
 
            };
            Assert.IsTrue(target.ValidateProductame(product));
 
        }
        #endregion
    }