RSS

Tag Archives: entity framework

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.

Advertisements
 
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: ,

Data Annotations in Class Generated by Entity Framework

How to add data annotations to the following class? The code is generated by Entity Framework, which means the file will be re-generated every time there’s a change to the EDMX file. When file is re-generated, all changes are wiped out.

public partial class Contact
{
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    //...
}

Solution

One way to do it is to modify T4 template file to include these annotations. I don’t think this is what you and I want. So, create another partial class in a separate file:

[MetadataType(typeof(ContactMetaData))]
public partial class Contact
{
}

public class ContactMetaData
{
    [Required]
    public string Firstname { get; set; }
    [Required]
    public string Lastname { get; set; }
}

Version attow: EF 6

 
Leave a comment

Posted by on December 2, 2014 in General

 

Tags: ,

Learning Paths

With so many tutorials, articles and resources available out there on the Internet, learning new programming language, framework and library become much more easier. However, as much as its advantage, readily available resources have also become its own problem: where should I start?

With so many options available, it can be confusing to even start learning. I present you my learning paths to solve this problem. Learning paths will guide you through learning programs for each subject of your interests. Think of this as a curriculum to the degree you want to get.

Most of the courses are from Pluralsight.com, but this learning paths are not limited to just Pluralsight.com. I also include some free courses from other sources. While I understand that you may have to pay for some of these courses, I can assure you that paying the subscription is worth it (especially Pluralsight!).

I will update this learning paths to include more subjects and courses in the future. Stay tuned!

Android

Level Course
0100 Get the Android SDK (http://developer.android.com/sdk/index.html)
0101 Prerequisite: 0100
Getting Started (http://developer.android.com/training/index.html)
0102 Introduction to Android Development (http://pluralsight.com/training/Courses/TableOfContents/android-intro)
0200 Prerequisite: 0101 or 0102
Android Async Programming and Services (http://pluralsight.com/training/Courses/TableOfContents/android-services)

AngularJS

Level Course
0100 AngularJS Fundamentals (http://pluralsight.com/training/Courses/TableOfContents/angularjs-fundamentals)
0200 Prerequisite: 0100
AngularJS In-Depth (http://pluralsight.com/training/Courses/TableOfContents/angularjs-in-depth)
0201 Prerequisite: 0100
Testing AngularJS From Scratch (http://pluralsight.com/training/Courses/TableOfContents/testing-angularjs-from-scratch)

ASP.NET MVC

Level Course
0100 ASP.NET MVC Fundamentals (http://pluralsight.com/training/Courses/TableOfContents/aspdotnet-mvc)
0200 Prerequisite: 0100
ASP.NET MVC 5 Fundamentals (http://pluralsight.com/training/Courses/TableOfContents/aspdotnet-mvc5-fundamentals)

ASP.NET Web API

Level Course
0100 Introduction to the ASP.NET Web API (http://pluralsight.com/training/Courses/TableOfContents/aspnetwebapi)
0200 Prerequisite: 0100
Web API v2 Security (http://pluralsight.com/training/Courses/TableOfContents/webapi-v2-security)
0201 Prerequisite: 0100
Web API Design (http://pluralsight.com/training/Courses/TableOfContents/web-api-design)

C#

Level Course
0100 C# Basic (http://csharp-station.com/Tutorial/CSharp)
0101 C# From Scratch (http://pluralsight.com/training/Courses/TableOfContents/csharp-from-scratch)
0102 Prerequisite: 0101
C# From Scratch – Part 2 (http://pluralsight.com/training/Courses/TableOfContents/csharp-from-scratch-part2)
0200 Prerequisite: 0100 or 0102
Object-Oriented Programming Fundamentals in C# (http://pluralsight.com/training/Courses/TableOfContents/object-oriented-programming-fundamentals-csharp)

Entity Framework

Level Course
0100 Getting Started with Entity Framework 5 (http://pluralsight.com/training/Courses/TableOfContents/entity-framework5-getting-started)
0200 Prerequisite: 0100
Entity Framework Code First Migrations (http://pluralsight.com/training/Courses/TableOfContents/efmigrations)

JavaScript & jQuery

Level Course
0100 W3Schools’s JavaScript Tutorial (http://www.w3schools.com/js/default.asp)
0101 JavaScript Fundamentals (http://pluralsight.com/training/Courses/TableOfContents/jscript-fundamentals)
0120 Prerequisite: 0100 or 0101
DO Factory’s JavaScript + jQuery Design Pattern Framework – JavaScript & Pattern Essentials (http://www.dofactory.com/products/javascript-jquery-design-pattern-framework)
0200 Prerequisite: 0100 or 0101
JavaScript Design Patterns (http://pluralsight.com/training/Courses/TableOfContents/javascript-design-patterns)
0300 Prerequisite: 0200
jQuery Fundamentals (http://pluralsight.com/training/Courses/TableOfContents/jquery-fundamentals)

WIF, Claims-based Identity, OAuth2

Level Course
0100 Introduction to Identity and Access Control in .NET 4.5 (http://pluralsight.com/training/Courses/TableOfContents/iac-intro)
0200 Prerequisite: 0100
Identity and Access Control in ASP.NET 4.5 (http://pluralsight.com/training/Courses/TableOfContents/iac-aspnet)
0201 Prerequisite: 0100
Identity and Access Control in WCF 4.5 (http://pluralsight.com/training/Courses/TableOfContents/iac-wcf)
0202 Prerequisite: 0100
Web API v2 Security (http://pluralsight.com/training/Courses/TableOfContents/webapi-v2-security)
0300 Prerequisite: 0200 or 0201 or 0202
Introduction to OAuth2, OpenID Connect and JSON Web Tokens (JWT) (http://pluralsight.com/training/Courses/TableOfContents/oauth2-json-web-tokens-openid-connect-introduction)
 
1 Comment

Posted by on September 15, 2014 in General

 

Tags: , , , , , , , , , , ,

Entity Framework Code First Migrations Commands

To enable migrations in your Entity Framework Code First project, the following commands guide you thru installation, enabling the migration, adding a migration and updating the database. All the commands are run on Package Manager Console.

Install EntityFramework package

PM> install-package EntityFramework

Enable Code First Migrations

PM> Enable-Migrations

Add a migration

PM> Add-Migration <migration_name>

Update the database

PM> Update-Database
 
Leave a comment

Posted by on June 11, 2013 in References

 

Tags: , ,

 
%d bloggers like this: