RSS

Author Archives: Quad Coders

Comparing Linq Join and GroupJoin

Tested on:
.Net 4.5
Entity Framework 6 – Github

This is a post comparing Linq Join and GroupJoin for querying database.

I created simple database consist of 2 tables, `Instructor` and `Class`, which has 1 to many relationship.

SQL instructor table

SQL class table

Entity Framework diagram looks like this.
EF diagram

The console app using Linq to join these 2 tables look like this.

using System.Linq;

namespace LinqJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new TestEntities())
            {
                var join = db.Instructor
                    .Join(
                        db.Class,
                        i => i.InstructorId,
                        c => c.InstructorId,
                        (i, c) => new
                        {
                            Instructor = i,
                            Class = c
                        })
                    .ToList();

                var groupJoin = db.Instructor
                    .GroupJoin(
                        db.Class,
                        i => i.InstructorId,
                        c => c.InstructorId,
                        (i, c) => new
                        {
                            Instructor = i,
                            Class = c
                        })
                    .ToList();

                var flattenGroupJoin = db.Instructor
                    .GroupJoin(
                        db.Class,
                        i => i.InstructorId,
                        c => c.InstructorId,
                        (i, c) => new
                        {
                            Instructor = i,
                            Class = c
                        })
                    .SelectMany(r => r.Class, (r, c) => new
                    {
                        Instructor = r.Instructor,
                        Class = c
                    })
                    .ToList();
            }
        }
    }
}

Following are the result.

** Join **

Visual Studio Debug
VS join result

SQL Profiler

SELECT 
    [Extent1].[InstructorId] AS [InstructorId], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent2].[ClassId] AS [ClassId], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[ClassNumber] AS [ClassNumber], 
    [Extent2].[InstructorId] AS [InstructorId1]
    FROM  [dbo].[Instructor] AS [Extent1]
    INNER JOIN [dbo].[Class] AS [Extent2] ON [Extent1].[InstructorId] = [Extent2].[InstructorId]

** Group Join **

`GroupJoin` result in grouped data by specified entity. In this case I grouped by `Instructor`.

Visual Studio Debug
VS groupjoin result

SQL Profiler

SELECT 
    [Project1].[InstructorId] AS [InstructorId], 
    [Project1].[FirstName] AS [FirstName], 
    [Project1].[LastName] AS [LastName], 
    [Project1].[C1] AS [C1], 
    [Project1].[ClassId] AS [ClassId], 
    [Project1].[Name] AS [Name], 
    [Project1].[ClassNumber] AS [ClassNumber], 
    [Project1].[InstructorId1] AS [InstructorId1]
    FROM ( SELECT 
        [Extent1].[InstructorId] AS [InstructorId], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[LastName] AS [LastName], 
        [Extent2].[ClassId] AS [ClassId], 
        [Extent2].[Name] AS [Name], 
        [Extent2].[ClassNumber] AS [ClassNumber], 
        [Extent2].[InstructorId] AS [InstructorId1], 
        CASE WHEN ([Extent2].[ClassId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Instructor] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Class] AS [Extent2] ON [Extent1].[InstructorId] = [Extent2].[InstructorId]
    )  AS [Project1]
    ORDER BY [Project1].[InstructorId] ASC, [Project1].[C1] ASC

** Flatten Group Join **

Visual Studio Debug
VS flatten groupjoin result

SQL Profiler

SELECT 
    [Extent1].[InstructorId] AS [InstructorId], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent2].[ClassId] AS [ClassId], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[ClassNumber] AS [ClassNumber], 
    [Extent2].[InstructorId] AS [InstructorId1]
    FROM  [dbo].[Instructor] AS [Extent1]
    INNER JOIN [dbo].[Class] AS [Extent2] ON [Extent1].[InstructorId] = [Extent2].[InstructorId]
Advertisements
 
Leave a comment

Posted by on May 14, 2019 in General

 

Tags: , , , , , ,

Iterator, Enumerator and Yield Keyword

Note: The example code below is only used to demonstrate my point, by all means it’s not SOLID.

In Object Oriented Programming
Iterating is a process of repeating similar steps or functions.
Enumerating is an action of going through an entire collection of objects.

In C#
Iterator refers to set of functions to be executed on a collection.
For example:

Func<User, User> deactivate = (User u) =>
{
    u.IsActive = false;
    return u;
};

Enumerator refer to an object type that result from iterating through a collection.
For example:

IEnumerable<User> result = Users.Select(x => deactivate(x));

Complete code:

public class User
{
    public bool IsActive { get; set; }
}

public IEnumerable<User> DeactivateUsers()
{
    User[] Users = {
        new User { IsActive = true },
        new User { IsActive = true },
        new User { IsActive = true }
    };

    Func<User, User> deactivate = (User u) =>
    {
        u.IsActive = false;
        return u;
    };

    IEnumerable<User> result = Users.Select(x => deactivate(x));
    return result;
}

Yield Keyword
Yield keyword allow you to indicate that the code where it is being used is an iterator. The code must return a type of IEnumerable or IEnumerable<T>.

Continue from example above. We can write it different way, such as:

public IEnumerable<User> DeactivateUsers()
{
    User[] Users = {
        new User { IsActive = true },
        new User { IsActive = true },
        new User { IsActive = true }
    };

    List<User> result = new List<User>();

    foreach (var u in Users)
    {
        u.IsActive = false;
        result.Add(u);
    }

    return result;
}

Or another way, which utilize yield keyword to return Enumerator object for us.

public IEnumerable<User> DeactivateUsers()
{
    User[] Users = {
        new User { IsActive = true },
        new User { IsActive = true },
        new User { IsActive = true }
    };

    foreach (var u in Users)
    {
        u.IsActive = false;
        yield return u;
    }
}

References:
MSDN
Stackoverflow

 
Leave a comment

Posted by on May 10, 2019 in General

 

Tags: , , , , , ,

Adding and Reference a Database Name in Data-Tier Application SQL Server

This apply to SQL Server Database Project.

Continuing from this post.

When adding data-tier application to SQL Server project, you can specify how to reference a database.

1. Right click on `References` under your project, select `Add Database Reference…`.
1

2. A dialog box will show up. Select your file data-tier application file with `Browse…` button.

3. Once data-tier application is selected, you can specify how you would like to reference a database. The default is show on screen show below.
3

The `Example usage` show how you can reference a database. For example, in your View, default way to reference a database is:

CREATE VIEW dbo.AccountView
	AS

SELECT *
FROM [$(test)].dbo.Account

4. (Optional) To change default way to reference a database, remove `Database variable` or set to anything you like. If `Database variable` is removed, you can reference a database the “normal” way.
2

CREATE VIEW dbo.AccountView
	AS

SELECT *
FROM test.dbo.Account
 
Leave a comment

Posted by on May 7, 2019 in General

 

Tags: , , , , , ,

Autofac Keyed Service Example

Note: Tested on Autofac 4.9.2.

Autofac Keyed Services provide convenience way to register multiple types of same interface with key identifier, see the documentation here.

It’s very useful feature when we want to choose between different implementations of same interface. See below for example:

`WebApiConfig.cs`
This is where we register our type in Autofac container.

var builder = new ContainerBuilder();

builder.RegisterType<CorporateService>()
    .As<IService>()
    .Keyed<IService>("Corporate");
builder.RegisterType<SchoolService>()
    .As<IService>()
    .Keyed<IService>("School");

GlobalContainer.Container = builder.Build();

`GlobalContainer.cs`
This is global object to store our container for resolving type later in application lifecycle.

using Autofac;

namespace QC
{
    public class GlobalContainer
    {
        public static IContainer Container { get; set; }
    }
}

`IService.cs`
This is sample service interface – don’t copy, make your own.

namespace QC.Service
{
    public interface IService
    {
        double GetRate();
    }
}

`CorporateService.cs`
This is sample service interface implementation – don’t copy, make your own.

namespace QC.Service
{
    public class CorporateService : IService
    {
        public double GetRate()
        {
            return 15;
        }
    }
}

`SchoolService.cs`
This is sample service interface implementation – don’t copy, make your own.

namespace QC.Service
{
    public class SchoolService : IService
    {
        public double GetRate()
        {
            return 10;
        }
    }
}

There are 2 options to resolve `IService` to type we desired (ie: Corporate or School).

1. Resolving Explicitly.
Documentation.

`CustomerController.cs`
Sample controller that choose which service to initiate.

using Autofac;
using QC.Service;
using System.Web.Http;

namespace QC.Controllers
{
    public class CustomerController : ApiController
    {
        [HttpGet]
        public IHttpActionResult GetRate()
        {
            // In real-world, we would have business logic to determine which service to initiate
            var service = GlobalContainer.Container.ResolveKeyed<IService>("Corporate");

            var rate = service.GetRate();
            return this.Ok(rate); // Return 15
        }
    }
}

2. Resolving with an Index (recommended).
Documentation.

`CustomerController.cs`

using Autofac.Features.Indexed;
using QC.Service;
using System.Web.Http;

namespace QC.Controllers
{
    public class CustomerController : ApiController
    {
        private readonly IIndex<string, IService> serviceIndexes;
        private readonly IService service;

        public CustomerController(IIndex<string, IService> serviceIndexes)
        {
            this.serviceIndexes = serviceIndexes;

            // In real-world, we would have business logic to determine which service to initiate
            this.service = this.serviceIndexes["Corporate"];
        }

        [HttpGet]
        public IHttpActionResult GetRate()
        {
            var rate = this.service.GetRate();
            return this.Ok(rate); // Return 15
        }
    }
}
 
Leave a comment

Posted by on April 1, 2019 in General

 

Tags: , , , , , ,

Entity Framework, .Net and SQL Server Table Valued Parameter

This is step by step setup of using SQL Server TVP (Table Valued Parameter) in .Net application with EF (Entity Framework). In this example, I use SQL Server 2016 (SP2-CU3), .Net 4.5.1 and EF 6.20.

1. Create a table to store data.

CREATE TABLE [dbo].[Something] (
    [Id]            INT	IDENTITY(1,1)   NOT NULL,
    [Name]          VARCHAR(150)        NOT NULL,
    [Status]        CHAR(1)             NOT NULL,
    CONSTRAINT [PK_Something_Id] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

2. Create `User Defined Table Type` in SQL Server. For simplicity, in this example the type’s columns are same as table I created on step 1. In real-world, the type’s columns could be significantly different than table where we store the data, it might even used for join with other tables.

CREATE TYPE [dbo].[udt_Something] AS TABLE (
	[Name]		VARCHAR(150)	NOT NULL,
	[Status]	CHAR(1)		NOT NULL,
);
GO

3. Create stored procedure to take parameter (of a `User Defined Table` type we created earlier) and perform necessary task to persist our data.

CREATE PROCEDURE [dbo].[sp_SaveSomething]
	@udt_Something [dbo].[udt_Something] READONLY
AS
BEGIN
    INSERT INTO [dbo].[Something]
        SELECT *
        FROM @udt_Something
END
GO

4. Create extension method to convert `IEnumerable<T>` object to a `DataTable` object. In order to use SQL TVP, we have to pass our parameter as a `DataTable`. This method will help convert our data to `DataTable` type.
Helper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace QC
{
    public static class Helper
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> enumerable, IEnumerable<string> orderedColumnNames)
        {
            var dataTable = new DataTable();

            // Get all properties of the object
            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            PropertyInfo[] readableProperties = properties.Where(w => w.CanRead).ToArray();

            // Get column
            var columnNames = (orderedColumnNames ?? readableProperties.Select(s => s.Name)).ToArray();

            // Add columns to data table
            foreach (string name in columnNames)
            {
                dataTable.Columns.Add(name, readableProperties.Single(s => s.Name.Equals(name)).PropertyType);
            }

            // Add rows to data table from object
            foreach (T obj in enumerable)
            {
                dataTable.Rows.Add(columnNames.Select(s => readableProperties.Single(s2 => s2.Name.Equals(s)).GetValue(obj)).ToArray());
            }

            return dataTable;
        }
    }
}

5. For the purpose of this example, let’s say we want to save a collection of objects. This is our object definition.
Something.cs

namespace QC
{
    public class Something
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Status { get; set; }
    }
}

6. Using EF, called stored procedure we created and pass in `SqlParameter`, which is a collection of objects that we converted to `DataTable`. Don’t forget to specify parameter type as `User Defined Table Type`.
DataAccess.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class DataAccess
    {
        public void Save(IEnumerable<Something> data)
        {
            // Columns for ordering, the order of the columns must be the same as user defined table type
            var orderedCols = new[] { "Name", "Status" };

            // SQL parameter to pass to stored procedure
            var param = new SqlParameter("@udt_Something", SqlDbType.Structured);
            param.Value = data.ToDataTable(orderedCols);
            param.TypeName = "dbo.udt_Something";

            try
            {
                // QCDB is our EF entities
                using (var db = new QCDB())
                {
                    // Call stored procedure and pass in table valued parameter
                    db.Database.ExecuteSqlCommand("EXEC dbo.sp_SaveSomething @udt_Something", param);
                }
            }
            catch
            {
                throw;
            }
        }
    }
}

7. Example of usage.
OpsController.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class OpsController : ApiController
    {
        public void SaveSomething()
        {
            var data = new List<Something>();
            data.Add(new Something { Id = 1, Name = "Chap", Status = "A" });
            data.Add(new Something { Id = 2, Name = "Stick", Status = "D" });

            var dataAccess = new DataAccess();
            dataAccess.Save(data);
        }
    }
}
 
Leave a comment

Posted by on January 18, 2019 in General

 

Tags: , , , , , , , , ,

Unit Test Internal Members

Applies to .Net application in Visual Studio 2010 and up.

Say I have C# project (call it QC project). In the project, I have class that has internal member(s) like this:

namespace QC
{
    public class PersonService
    {
        public bool PromotePerson(string userName)
        {
        }

        internal bool FindPerson(string userName)
        {
        }
    }
}

For unit test, I’d created separate project (call it QCTest project). Because internal members can only be accessed in same assembly, I won’t be able to invoke `QC.PersonService.FindPerson(string userName)` method from QCTest project.

To overcome this issue, in `AssemblyInfo.cs` of project being tested (in my case, QC project), simply add

[assembly: InternalsVisibleTo("QCTest")]

After rebuilding my solution, I am able to access `QC.PersonService.FindPerson(string userName)` in my QCTest project.

As an added bonus, if you use mock library like Moq or Rhino and try to mock internal members, you most likely need to add:

[assembly: InternalsVisibleTo("DynamicProxyGenAssembly2")]

What is `DynamicProxyGenAssembly2` ?

DynamicProxyGenAssembly2 is a temporary assembly built by mocking systems that use CastleProxy like Moq or NSubsitute. It is generated when the mock is needed and disposed of after the tests are finished.

Reference: https://stackoverflow.com/a/17179923

 
Leave a comment

Posted by on January 18, 2019 in General

 

Tags: , , , , , ,

Unit Testing DTO Classes

Reason why I unit test my DTO (Data Transfer Object) classes are because I pass these classes to client app and client app might use them and expect certain properties in response. Sometimes I change DTO properties that I used in client app and forget to refactor client app.
By unit testing my DTO classes, it acts as reminder for me to refactor client app whenever DTO properties (or its name) changes.

For example, I have Person DTO.

Person.cs

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Age { get; set; }
}

To unit test, I would create PersonMirror DTO.

public class PersonMirror
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Age { get; set; }
}

Then use following helper class to unit test my DTO.

public class TestHelper
{
    public static void CompareType()
    {
        var errorMessage = "Type: {0}; Property: {1}";

        var instance = Activator.CreateInstance(typeof(T));
        var instanceType = instance.GetType();
        var mirror = Activator.CreateInstance(typeof(TMirror));
        var mirrorType = mirror.GetType();
        var mirrorProperties = mirrorType.GetProperties();

        // Remove 'Mirror' convention
        var mirrorTypeName = mirrorType.Name;
        if (mirrorType.Name.IndexOf("Mirror") > 0)
        {
            mirrorTypeName = mirrorType.Name.Substring(0, mirrorType.Name.IndexOf("Mirror"));
        }

        // Compare type name
        Assert.AreEqual(mirrorTypeName, instanceType.Name);

        // Compare properties
        foreach (var mirrorProperty in mirrorProperties)
        {
            var instanceProperty = instanceType.GetProperty(mirrorProperty.Name);

            Assert.IsNotNull(instanceProperty, string.Format(errorMessage, instanceType.FullName, mirrorProperty.Name));
        }
    }
}

Usage (using MS Test):
PersonTest.cs

[TestClass]
public class PersonTest
{
    [TestMethod]
    public void TestPersonDto()
    {
        TestHelper.CompareType<Person, PersonMirror>();
    }    
}
 
Leave a comment

Posted by on January 18, 2019 in General

 

Tags: , , , , ,

 
%d bloggers like this: