Entity Framework: Use Expression Trees to Build Dynamic Queries or Filter using Expression tree

Standard

Expression trees represent code in a tree-like data structure, where each node is an expression, for example, a method call or a binary operation such as x < y.

You can compile and run code represented by expression trees. This enables dynamic modification of executable code, the execution of LINQ queries in various databases, and the creation of dynamic queries. Entity Framework or LINQ to SQL operates with expression trees to construct SQL queries from lambda expressions.

Look at below syntax:

void Delete<T>(Expression<Func<T, bool>> expression) where T : class, new()

A “predicate” is any device that says “yes” or “no” to the question “is this thing a member of that set?” So a predicate for the set “integers even positive integers” would be x=> x > 0 && x % 2 == 0.

This method probably has the semantics of “delete from the collection all members of the collection that are in the set identified by the predicate”.  The predicate is passed to the method in the form of an expression tree, which is a way of passing the structure of the predicate in a manner that can be analyzed at runtime and transformed.

  • Use Expression<Func<EntityName, bool>> rather than Expression<Predicate<EntityName>>, because that’s what Queryable.Where expects
  • Pass it directly in .Where, combining them using multiple .Where calls instead of &&.Unfortunately there’s no way to use Predicate<T> in EF linq since it’s impossible to map it on SQL query. This can be done with Expressions only because they can be parsed and converted to SQL.In fact there are 4 language features that made linq possible:
    1. Extension methods
    2. Type inference
    3. Closures
      and for linq2sql especially
    4. Expressions

    Code Example

public Course EditCourse(Course obj)
{
   return courseRepo.Update(obj, x => x.CourseId == obj.CourseId);
}
public virtual T Update(T obj, Expression<Func<T, bool>> match)
{
     using (var context = new PluralSightContext())
     {
        if (obj == null)
           return null;
 
         T existing = context.Set<T>().SingleOrDefault(match);
 
         if (existing != null)
         {
              context.Entry(existing).CurrentValues.SetValues(obj);                    
              context.SaveChanges();
         }
         return existing;
      }
}

So now map Expression<Func<T, bool>> in above example: bool is return type and T refers to Course Entity, so Expression x => x.CourseId == obj.CourseId stores the info about that expression and that there’s a T x, that you’re accessing the property CourseId (T corresponds to Course entity)

Calling the == operator with the int value provided by user. When EF looks at that, it might turn it into something like [Course_Table].[CourseID_Column] == parameter value

MSDN Reference Link

MSDN Reference Link2

 

Advertisements

Entity Framework: error The ObjectContext instance has been disposed and can no longer be used for operations that require a connection

Standard

“The ObjectContext instance has been disposed and can no longer be used for operations that require a connection”: this error is quite common for all those operation which involves entities containing navigational properties/graph data/related data

2016-02-18 07_23_25-MyHomepage (Debugging) - Microsoft Visual Studio

In the above case, I was updating Course entity while Technology entity was navigational property in Course. As you can see all properties of Course have been pulled from database except of Technology data. Just go through code below:

Snapshot of Context class code:

 

2016-02-18 07_28_46-MyHomepage - Microsoft Visual Studio

Repository Class code:

        public T GetByID(int key)
        {
            using (var context = new PluralSightContext())
            {
                T existing = context.Set<T>().Find(key);
                return existing;
            }
        }
 
        public virtual T Update(T obj, int key)
        {
            using (var context = new PluralSightContext())
            {
                if (obj == null)
                    return null;
 
                T existing = GetByID(key);
                if (existing != null)
                {
                    context.Entry(existing).CurrentValues.SetValues(obj);
                    context.SaveChanges();
                }
                return existing;
            }
        }

Solution 1 (least appropriate):

Since lazy loading is activated as you can see from context class constructor code, By default Entity Framework uses lazy-loading for navigation properties. That’s why these properties should be marked as virtual – EF creates proxy class for your entity and overrides navigation properties to allow lazy-loading, so in order to resolve your problem either use eager loading (.include(), .attach()) or explicit loading(.load()) and remove Virtual keyword from navigational properties. But I wont recommend this technique because it has performance impact, it will start loading all graph data for all entities, in order to make it happen only for single entity you have to write extra line of codes in context class.

MSDN Reference Link on Loading Strategies

Solution 2:

As you can see from above code we are creating two context first for GetByID function and the for Update function, I have using block around existing usage. Which disposes context before entities are returned. When some code later tries to use lazy-loaded navigation property, it fails, because context is disposed at that moment.

I am ending your context prematurely: a DbContext should be available throughout the unit of work being performed, only disposing it when you’re done with the work at hand. In the case of ASP.NET, a unit of work is typically the HTTP request being handled.

So if we move GetByID logic under one using statement, so this will resolve above issue:

        public virtual T Update(T obj, int key)
        {
            using (var context = new PluralSightContext())
            {
                if (obj == null)
                    return null;
 
                T existing = context.Set<T>().Find(key);
                if (existing != null)
                {
                    context.Entry(existing).CurrentValues.SetValues(obj);
                    context.SaveChanges();
                }
                return existing;
            }
        }

Entity Framework: Avoid Saving Related Data/Graph Data/Navigation Property with Entity

Standard

First solution – use the same context for loading department and saving employee:

using (var context = new YourContext())
{
    var employee = new Employee();
    context.Employees.AddObject(employee);
    employee.Department = context.Departments.Single(d => d.Id == departmentId);
    context.SaveChanges();
}

Second solution – connect entities to the context separately and after that make reference between entities:

var employee = new Employee();
var department = GetDepartmentFromSomewhere(departmentId);

using (var context = new YourContext())
{
    context.Employees.AddObject(employee);
    context.Departments.Attach(department);
    employee.Department = department;
    context.SaveChanges();
}

Third solution – correct state of the department manually so that context doesn’t insert it again:

var employee = new Employee();
employee.Department = GetDepartmentFromSomewhere(departmentId);
using (var context = new YourContext())
{
    context.Employees.AddObject(employee);
    context.ObjectStateManager.ChangeObjectState(employee.Department, 
                                                 EntityState.Unchanged);
    context.SaveChanges();
}

Fourth solution – change related entity to null:

var employee = new Employee();
using (var context = new YourContext())
{
    employee.Department = null;
    context.Employees.AddObject(employee);
    context.SaveChanges();
}

Reference

Entity Framework: Debugging Error from DbEntityValidationResult and DbentityValidationException

Standard

Use below code to find exact issue for validation exception by Entity Framework:

catch (DbEntityValidationException Ex)
{
    foreach (var Errors in Ex.EntityValidationErrors)
    {
        foreach (var valError in Errors.ValidationErrors)
        {
            Trace.TraceInformation("Property: {0} Error: {1}", valError.PropertyName, valError.ErrorMessage);
        }
    }
}

 

Or Just place below code in watch window as soon you hit by exception during debugging

((System.Data.Entity.Validation.DbEntityValidationException)$exception).EntityValidationErrors.First().ValidationErrors.First().ErrorMessage

 

2016-02-16 16_42_02-MyHomepage (Debugging) - Microsoft Visual Studio

Entity Framework : Data Annotations – Foreign Key Attribute

Standard

ForeignKey attribute can be applied to properties of a class. Default Code-First convention for ForeignKey relationship expects foreign key property name match with primary key property.
1) Use ForeignKey (with an associated property) – version 1

[Table("WIDGETENTITIES")]
public class WidgetEntity {

    [Column("WIDGETENTITY_ID")]
    public int Id { get; set; }

    [Column("WIDGETSEQUENCE_ID")]
    public int WidgetSequenceId { get; set; }

    [ForeignKey("WidgetSequenceId")] //Has to be a property name, not table column name
    public WidgetSequence Sequence { get; set; }

    // and other properties that map correctly
}

[Table("WIDGETSEQUENCES")]
public class WidgetSequence { 

    [Column("WIDGETSEQUENCE_ID")]
    public int Id { get; set; }

    [Column("NUMBER")]
    public int Number { get; set; }
}

1.2) Use ForeignKey (with an associated property) – version 2

[Table("WIDGETENTITIES")]
public class WidgetEntity {

    [Column("WIDGETENTITY_ID")]
    public int Id { get; set; }

    [ForeignKey("Sequence")] //Has to be a property name, not table column name
    [Column("WIDGETSEQUENCE_ID")]
    public int WidgetSequenceId { get; set; }

    public WidgetSequence Sequence { get; set; }

    // and other properties that map correctly
}

[Table("WIDGETSEQUENCES")]
public class WidgetSequence { 

    [Column("WIDGETSEQUENCE_ID")]
    public int Id { get; set; }

    [Column("NUMBER")]
    public int Number { get; set; }
}

2) You can also use the InversePropertyAttribute.

[Table("WIDGETENTITIES")]
public class WidgetEntity {

    [Column("WIDGETENTITY_ID")]
    public int Id { get; set; }

    [InverseProperty("WidgetEntities")]
    public WidgetSequence Sequence { get; set; }

    // and other properties that map correctly
}

[Table("WIDGETSEQUENCES")]
public class WidgetSequence { 

    [Column("WIDGETSEQUENCE_ID")]
    public int Id { get; set; }

    [Column("NUMBER")]
    public int Number { get; set; }

    public virtual List<WidgetEntity> WidgetEntities { get; set; }
}

Reference

Common Problems with Entity Framework for Importing Store Procedure

Standard
Three common issues occurs while adding store procedure to edmx file of Entity Framework

Introduction

I’ve seen three problems with updating a Stored Procedure after adding it to the entity framework:

  1. The Function Import created to call the Stored Procedure wasn’t updated (incorrect, out-of-date parameters)
  2. The Complex Type created for the Stored Procedure result set wasn’t updated (incorrect, out-of-date fields)
  3. Get Column Information in Edit Function Import Popup doesn’t return any column

Background

When a stored procedure is added to a conceptual model, it is called a function import. Adding a function import allows you to call the corresponding stored procedure from your application code. A function import can return collections of simple types, EntityTypes, or ComplexTypes, or no value.

To Create a function Import:

  1. Do one of the following from the Model Browser:
    • Open the Stored Procedures folder (in the storage model information) and double-click a stored procedure that does not have a corresponding function import.

    – OR –

    • Right-click the Function Imports folder (in the EntityContainer node of the conceptual model information) and then select Add Function Import.

    The Add Function Import dialog box appears.

  2. Fill in the settings for the new function import.
    • Specify the stored procedure for which you are creating a function import in the Stored Procedure Name field. This field is a drop-down list that displays all the stored procedures in the storage model. If the desired stored procedure is not available, you may need to update your storage model.
    • Specify the name of the function import in the Function Import Name field.
    • Specify one of the four basic return types: None, Scalars, Complex, or Entities, and select the specific return type from the available drop-down list. If you choose Complex, the Entity Designer can create a new complex type with properties that correspond to the columns returned by the stored procedure.
    1. Click Get Column Information to retrieve column information.
    2. Click Create New Complex Type.
    3. Edit the name of the complex type in the Complex drop-down list.

      When you click OK, a new complex type is added to the conceptual model and the return type of the function import is set to this new type

  3. Click OK.
    Reference: [http://msdn.microsoft.com/en-us/library/vstudio/bb896231%28v=vs.100%29.aspx]

Points of Interest

First Problem:

Open the .edmx file (in the GUI designer), right-click in some open space and select “Update Model from Database”.

Click Finish (when able) on the pop-up window, and your Stored Procedure signature should be updated (along with any Function Imports).

Second Problem:

Open the “Model Browser” window while you have the .edmx open. In the EntityContainer… node you should see a “Function Imports” node.

Inside this you’ll see your Function Import mapped to your Stored Procedure, double-click it to open the same window you used to create it, but now populated with its data.

Now, click “Get Column Information” (look at the grid below the button to see what will be changed), then the “Update” button next to the “Complex” radio button choice. Click OK, and the Complex Type for your result set should be updated.

Third Problem:

Just add SET FMTONLY OFF after BEGIN statement in Stored Procedure and SET FMTONLY ON before END statement in the same Stored Procedure.

CREATE PROCEDURE [my_SP1]
    @ID            INT
AS
BEGIN
    SET FMTONLY OFF
    
    SELECT col1, col2
    FROM   table1
    
    SET FMTONLY ON
END

And most importantly please don’t add anything manually to .edmx, .context .tt and POCO entities [except manual created entity classes] because updating model from database will eradicate all changes made manually, use POCO classes as much as possible.

Happy Coding.

History

version 1.0

About the Author

Muhammad Hassan Tariq

Software Developer TRG/IBEX-Global
Pakistan Pakistan