RSS

Tag Archives: entity framework

Expression Func vs. Func

Func
`Func<>` is a lambda expression. A delegate. A pointer to a method. Runtime engine can not “look” inside `Func<>`, it can only execute `Func<>`.

Example usage:

Func<int> haveFunc = () => 15; // return 15

Expression Func
`Expression<Func<>>` is an expression tree. It’s a lambda expression in form of tree data structure. It only hold metadata or information and composition of the contained lambda expression. Runtime engine can look “inside” `Expression<Func<>>` and translate it to other statement you need.

Example usage:

Expression<Func<int>> notMuchFunc = () => 25;

Why is it important ?
Misuse of this could be cause compile error or in the case of EF, a performance hit.

Compile Error Example
Linq has few methods that take `Func<>` or `Expression<Func<>>` parameter, for example `Where` extension method, as seen in signature below.

System.Linq.Enumerable
public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);

System.Linq.Queryable
public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate);

Depend on parameter type we pass in, compiler know which extension method to call.

In the code below, ProcessUsers3 throw compiler error. Because we pass in `Expression<Func<>>`, compiler think we are calling `Where` extension method which accept `IQueryable<>` but `this.GetUserNames()` return `IEnumerable<>`.

The workaround is to convert `IEnumerable<>` to `IQueryable<>` using `.AsQueryable()` as can be seen on ProcessUsers4.

public IEnumerable<string> GetUserNames()
{
    return new[] { "user1", "user2" };
}

public void ProcessUsers()
{
    // Return type IEnumerable<string>
    var result = this.GetUserNames()
        .Where((item) => !string.IsNullOrEmpty(item));
}

public void ProcessUsers2()
{
    // Return type IEnumerable<string>
    Func<string, bool> predicate = (item) => !string.IsNullOrEmpty(item);
    var result = this.GetUserNames()
        .Where(predicate);
}

public void ProcessUsers3()
{
    // Compile error
    // We pass in Expression<Func<>> to Where extension method, compiler think we are calling the Where extension method for IQueryable<> - this.GetUserNames() return IEnumerable<>
    Expression<Func<string, bool>> predicate = (item) => !string.IsNullOrEmpty(item);
    var result = this.GetUserNames()
        .Where(predicate);
}

public void ProcessUsers4()
{
    Expression<Func<string, bool>> predicate = (item) => !string.IsNullOrEmpty(item);

    // Return type IQueryable<string>
    var result = this.GetUserNames()
        .AsQueryable()
        .Where(predicate);
}

Performance Hit Example
In the code below, both methods are legal, compile without error and run without error.

On GetAppConfigurations1, we query EF context by passing `Func<>`, a lambda expression. Because the context can not “look” inside the lambda expression (it can only execute it), Linq would get all AppConfiguration and run `Func<>` on each record, which increase load time.

On GetAppConfigurations2, we query EF context by passing `Expression<Func<>>`, an expression tree. It allow Linq to “look” inside the expression tree, read its metadata and composition than figure out the best and most perfomant way to query AppConfiguration with filter condition we specified.

public IEnumerable<AppConfiguration> GetAppConfigurations1()
{
    Func<AppConfiguration, bool> filter = (config) => config.Id == 1;

    using (var context = new QCEntities())
    {
        var result = context.AppConfiguration.Where(filter);
        return result;
    }
}

public IEnumerable<AppConfiguration> GetAppConfigurations2()
{
    System.Linq.Expressions.Expression<Func<AppConfiguration, bool>> filter = (config) => config.Id == 1;

    using (var context = new QCEntities())
    {
        var result = context.AppConfiguration.Where(filter);
        return result;
    }
}

References
Stackoverflow

Advertisements
 
Leave a comment

Posted by on June 10, 2019 in General

 

Tags: , , , , , , , , ,

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]
 
Leave a comment

Posted by on May 14, 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: , , , , , , , , ,

Fix Linq Generate ‘ESCAPE’ Keyword on Contains/StartsWith/EndsWith Predicate and Cause Performance Hit

When using Linq to Entity (or Linq to SQL) with these predicates, Linq will generate ‘ESCAPE’ keyword in SQL statement which cause performance hit.

This code:

public IEnumerable GetUser(string filter)
{
    using (var db = new SomeEntities())
    {
        var result = db.User
            .Where(u => u.DisplayName.Contains(filter))
            .ToList();
    }
}

Will generate this sql statement:

SELECT [Extent1].[DisplayName] AS [DisplayName]
FROM   (SELECT [User].[DisplayName] AS [DisplayName]
        FROM   [dbo].[User] AS [User]) AS [Extent1]
WHERE  [Extent1].[DisplayName] LIKE '%garlan%' /* @p__linq__0 */ ESCAPE '~'

The `ESCAPE ‘~’` could potentially cause performance degradation.

Solution to this issue is to pass in constant into Contains predicate. It’s easier said than done.
If we have method like `GetUser` above which take ‘filter’ parameter, it’s not possible to convert to constant.
To overcome this, we have to create method that return predicate with constant.

This solution is proposed here.

Summary is to create extension class.

using System;
using System.Linq.Expressions;

namespace Blahblahblah
{
    public static class PredicateConstantCreator
    {
        public static Expression EmbedConstant(this Expression expression, TConstant constant)
        {
            var body = expression.Body.Replace(expression.Parameters[1], Expression.Constant(constant));

            return Expression.Lambda(body, expression.Parameters[0]);
        }

        private static Expression Replace(this Expression expression, Expression searchEx, Expression replaceEx)
        {
            return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
        }

        internal class ReplaceVisitor : ExpressionVisitor
        {
            private readonly Expression from;
            private readonly Expression to;

            public ReplaceVisitor(Expression from, Expression to)
            {
                this.from = from;
                this.to = to;
            }

            public override Expression Visit(Expression node)
            {
                return node == this.from ? this.to : base.Visit(node);
            }
        }
    }
}

And use it like this:

Expression predicate = (item, filterTerm) => item.DisplayName.Contains(filterTerm);
var result = User
    .Where(predicate.EmbedConstant(filter))
    .ToList();

Linq will generate sql statement like this:

SELECT [Extent1].[DisplayName] AS [DisplayName]
FROM   (SELECT [User].[DisplayName] AS [DisplayName]
        FROM   [dbo].[User] AS [User]) AS [Extent1]
WHERE  [Extent1].[DisplayName] LIKE '%garlan%' /* @p__linq__0 */

Alternative solution is to add function to generated XML file in EDMX. This solution is lay out here.

 
Leave a comment

Posted by on October 23, 2018 in General

 

Tags: , , , , ,

Adding ASP.Net Identity (with OWIN) to Existing MVC 5 Project

The simple way is start empty MVC project with authentication and pick and choose ASP.Net Identity part you want to implement.

Here is the summary:

  • Install dependencies. See https://www.asp.net/identity/overview/getting-started/introduction-to-aspnet-identity, scroll down to “Components of ASP.Net Identity”
    • Entity Framework
    • OWIN
    • Microsoft.AspNet.Identity.Core
    • Microsoft.AspNet.Identity.EntityFramework
    • Microsoft.AspNet.Identity.Owin
    • Microsoft.Owin
    • Microsoft.Owin.Host.SystemWeb
    • Microsoft.Owin.Security
    • Microsoft.Owin.Security.Cookies
    • Microsoft.Owin.Security.OAuth
    • Microsoft.Owin.Security.Google (if you want allow external login with Google)
    • Microsoft.Owin.Security.Facebook (if you want allow external login with Facebook)
    • Microsoft.Owin.Security.Twitter (if you want allow external login with Twitter)
    • Netwonsoft.Json
  • In Web.config
    • Under
      <system.web>

      Add

      <authentication mode="None" />

      This is to remove IIS authentication, because we are going to use OWIN

    • Add all OWIN dependencies
  • Add Startup.cs. This is a startup class run by OWIN
    • Don’t forget its dependency, Startup.Auth.cs in App_Start folder
  • Add all classes in IdentityConfig.cs
    • There are 4 classes, EmailService, SmsService, ApplicationUserManager, ApplicationSignInManager. I’d like to separate them into different files
  • Add all classes in IdentityModels.cs
    • There are 2 classes, ApplicationUser, ApplicationDbContext. I’d like to separate them into different files

Those are the basic setup needed for ASP.Net Identity. The rest of the setup is front-end side, AccountController and ManageController, which has its own view models and CSHTML.

 
Leave a comment

Posted by on December 27, 2016 in General

 

Tags: , , ,

Rename Table and Column Name in EF Code First

Business rules change over the time. For developers, this can be frustrating. Especially after you have spent enormous amount of time to name your objects properly. After all, we all know naming is the most prominent process of the development.. 🙂

Luckily, in Entity Framework, you can change table names quite easily.

Two ways, using data annotation and Fluent API. (Code is in Entity Framework 6)

Data Annotations

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

// Change table name to People
[Table("People")]
public class Employee
{
    // Change column name to PersonId
    [Column("PersonId")]
    public int Id { get; set; }
    public Guid DepartmentId { get; set; }
    public int CompanyId { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
}

FluentAPI

Context file (inherit from DbContext)

using System.Data.Entity;

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // Change column name to PersonId
    modelBuilder.Entity<Employee>()
        .Property(p => p.Id)
        .HasColumnName("PersonId");

    // Change table name to People
    modelBuilder.Entity<Employee>()
        .ToTable("People");
}
 
Leave a comment

Posted by on June 18, 2015 in General

 

Tags: , ,

Composite Key (Multi Columns Primary Key) in EF Code First

Composite Key is basically Primary Key that spans multiple columns. Look like this:

composite-key-multi-columns-primary-key-in-ef-code-first-1

composite-key-multi-columns-primary-key-in-ef-code-first-2

To achieve this in EF Code First, all you need to add is Key and Column data annotation. (I am using Entity Framework 6).

Column attribute takes in Order parameter which you can use to specify the order as it appears on the table.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Employee
{
    [Key]
    [Column(Order = 1)]
    public int Id { get; set; }

    [Key]
    [Column(Order = 2)]
    public Guid DepartmentId { get; set; }

    [Key]
    [Column(Order = 3)]
    public int CompanyId { get; set; }

    public string Firstname { get; set; }
    public string Lastname { get; set; }
}
 
2 Comments

Posted by on June 11, 2015 in General

 

Tags: ,

 
%d bloggers like this: