Entity Framework Multitenancy

Introduction

Multitenancy is currently a hot topic in web development. Azure, SharePoint, and many other frameworks are offering multitenant options, because it totally makes sense to make a better use of a server by hosting many different services.

When it comes to data, there are usually three different strategies:

  • Separate databases: each tenant is stored in its own database; different connection strings must be provided, one for each tenant;
  • Shared database, separate schemas: all tenant’s data live in the same database and even share table names, but in different schemas;
  • Shared database, shared schema tenants share the same physical tables,  but use a discriminator column for distinguishing between them.

Let’s explore how we can use each of these techniques in Entity Framework (Code First, of course – is there another?).

Prerequisites

First, we need to have a way to obtain the current tenant, specifically, a tenant id or code. To simplify, let’s just assume a simple interface:

public static class TenantConfiguration

{

    public static String GetCurrentTenantId()

    {

        //doesn't matter

    }

}

You are free to implement this in any way you want.

We’ll also have a data context:

public class MultitenantContext : DbContext

{

    public DbSet<MultitenantEntity> MultitenantEntities { get; set; }

}

Separate Databases

We need to inject the different connection strings through the constructor:

public MultitenantContext() : base(GetConnectionString())

{

}

 

private static String GetConnectionString()

{

    var currentTenant = TenantConfiguration.GetCurrentTenantId();

    return ConfigurationManager.ConnectionStrings[currentTenant].ConnectionString;

}

This is a simple strategy that relies on having one connection string per tenant id, but others exist, of course.

Shared Database, Separate Schemas

Another option is to have each tenant in its own schema. For that, we need to leverage the OnModelCreating method for configuring the model:

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

    var currentTenant = TenantConfiguration.GetCurrentTenantId();

 

    modelBuilder.Types().Configure(x =>

    {

        x.ToTable(x.ClrType.Name, currentTenant);

    });

 

    base.OnModelCreating(modelBuilder);

}

Again, a simple example: each type gets mapped to an equally-named table and to a schema that is identical to the tenant id. Pay attention to this: SQL Server and other RDBMSs allows users to have a default schema, so in theory, if you use integrated security, you may leave out the explicit schema. However, Entity Framework will always include the schema with the database objects, so you have to explicitly configure it, as we have.

Shared Database, Shared Schema

The final option depends on a discriminator column that is not mapped, but contains a different value for each tenant. Again, we need to configure the model accordingly (warning: reflection ahead):

private static void Map<T>(EntityMappingConfiguration<T> cfg) where T : class

{

    var currentTenant = TenantConfiguration.GetCurrentTenantId();

 

    cfg.Requires("Tenant").HasValue(currentTenant);

}

 

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

    var modelConfiguration = modelBuilder.GetType().GetProperty("ModelConfiguration", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(modelBuilder, null);

    var entities = modelConfiguration.GetType().GetProperty("Entities", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(modelConfiguration, null) as IEnumerable<Type>;

 

    foreach (var entity in entities)

    {

        var entityTypeConfiguration = modelBuilder.GetType().GetMethod("Entity").MakeGenericMethod(entity).Invoke(modelBuilder, null);

        var mapMethod = entityTypeConfiguration.GetType().GetMethods().First(m => m.Name == "Map");

 

        var localMethod = this.GetType().GetMethod("Map", BindingFlags.Static | BindingFlags.NonPublic).MakeGenericMethod(entity);

        var delegateType = typeof(Action<>).MakeGenericType(localMethod.GetParameters().First().ParameterType);

 

        var del = Delegate.CreateDelegate(delegateType, localMethod);

 

        mapMethod.Invoke(entityTypeConfiguration, new Object[] { del });

    }

 

    base.OnModelCreating(modelBuilder);

}

This code is required because we need to iterate through all mapped entities, and Entity Framework doesn’t expose everything as public APIs. Some parts could be cached for performance or slightly improved, but I leave that as an exercise to you. In the end, all queries to entities will take an additional restriction “WHERE Tenant = @tenant”, where @tenant will take a different value for each tenant and Tenant is a physical column that isn’t mapped, nor does it need to be.

Conclusion

We’ve seen several techniques for mapping multitenant Entity Framework contexts; ultimately, the one we choose will depend on a number of factors. It will mostly be about having more or less isolation of your data.

Lesser-Known NHibernate Features: Calculated Properties

With NHibernate you can have entity properties that are the result of a SQL expression (unlike other O/RMs). It is also possible to add arbitrary SQL restrictions to collections of an entity.

First, here’s how we define a calculated property:

public class MyEntity

{

    //rest goes here

    public Int32 ? MyCalculatedProperty { get; protected set; }

}

 

mapper.Class<MyEntity>(c =>

{

    //rest goes here

    c.Property(x => x.MyCalculatedProperty, x =>

    {

        x.Formula("(SELECT MAX(SomeTable.Something) FROM SomeTable WHERE SomeTable.Id = Id)");

        x.Insert(false);

        x.Update(false);

    });

});

NHibernate is clever enough to find out that the un-prefixed Id refers to the entity’s table. This is a silly example, but I think you get the picture. Remember that this is plain SQL, not HQL, and will not be translated in any way.

As for collection restrictions, a simple example:

public class MyEntity

{

    //rest goes here

    public virtual IEnumerable<MyIssue> RecentIssues { get; protected set; }

}

 

mapper.Class<MyEntity>(c =>

{

    //rest goes here

    c.Set(x => x.RecentIssues, x =>

    {

        c.Where("(date >= (GETDATE() - 7))");

        //rest goes here

    }, c => c.OneToMany());

});

Notice that I am mapping the RecentIssues collection as IEnumerable<T>, this is because otherwise I would have to check if the values being added matched the desired constraint (“>= GETDATE() – 7”, the last 7 days). Certainly possible, but I leave it as an exercise to you, dear reader! Of course, GETDATE() is a SQL Server function, the restrictions can only be specified in native SQL.

Stay tuned for more!

Lesser-known NHibernate Features: Filters

Unlike other OR/Ms – which, as always, shall remain unnamed – NHibernate offers a couple of ways to automatic filter results. Basically, we have two options:

  • Static restrictions;
  • Dynamic restrictions, or filters.

Because filters offer everything that static restrictions do and more, we’ll focus on filters.

A filter can specify a restriction, in terms of a SQL clause, to either an entity as a whole (the class, not a specific query) or to a collection (bag, set, list, map, array, etc).

For example, imagine you have a table that holds values that can be translated and a translation table for that purpose:

image

You will want to retrieve only the translation for the current culture. A domain model could look like:

image

We would like to apply a restriction to the Translations property of Translatable, so as to filter the translations by the current culture.

First, we need to create a filter, this is done at Configuration level:

cfg.AddFilterDefinition(new FilterDefinition("CurrentCulture", string.Empty, new Dictionary<string, IType> { { "Culture", NHibernateUtil.String } }, false));

The restriction can be defined on the filter itself, or per entity or collection. In this case, I didn’t specify it on the filter (string.Empty), so I will need to do it at the collection level:

mapper.Class<Translatable>(x =>

{

        //rest goes here

        x.Set(y => y.Translations, y =>

        {

            //rest goes here

            y.Filter("CurrentCulture", z =>

            {

                z.Condition("Culture = :Culture");

            });

        });

    }

);

A filter needs to be explicitly made active, and, if it contains parameters, all of its parameters must be set:

session.EnableFilter("CurrentCulture").SetParameter("Culture", CultureInfo.CurrentCulture.Name);

Now, whenever the Translations collection is retrieved, either through a SELECT or an INNER JOIN, the “Culture = :Culture” restriction – where, of course, :Culture is replaced by the current parameter value – will be applied automatically, together with the foreign key restriction.

The other option is to filter entities as a whole. Remember soft deletes? I wrote two posts on them (here and here). Instead of using static restrictions, we can instead use filters:

cfg.AddFilterDefinition(new FilterDefinition("SoftDeletes", "deleted = 0, new Dictionary<string, IType>(), true));

 

mapper.Class<Record>(x =>

{

    x.Filter("SoftDeletes", y => {});

    x.Set(y => y.Children, y =>

        {

            y.Filter("SoftDeletes", z => {});

        });

});

In this example, I define the restriction string on the filter itself, and I apply it to both the Record entity and its Children collection. This time, no parameters, but we still need to enable the filter before we issue a query:

session.EnableFilter("SoftDeletes");

If for any reason you want to disable a filter, it’s easy:

session.DisableFilter("SoftDeletes");

Or even get its definition:

var filter = sessionFactory.GetFilterDefinition("SoftDeletes");

Enjoy your filters!

Lesser-Known NHibernate Features: Mapping By Convention

Did you know that NHibernate, like other O/RMs out there, allows you to map classes by convention? Yes, it’s true… Smile Let me show you how!

First, you need to create an instance of the appropriately-named ConventionModelMapper:

var mapper = new ConventionModelMapper();

Next, you need to tell it where is the assembly (or assemblies) containing the entities you want to map:

var mappings = mapper.CompileMappingFor(typeof(MyEntity).Assembly.GetExportedTypes());

Finally, you need to add the generated mappings to a Configuration instance:

cfg.AddMapping(mappings);

That’s all it takes! Really! Winking smile

Granted, this is very convenient, but we don’t know much what is happening inside; for example, what id generation strategy is it using? By default, it uses native, which means it will use the native strategy of the database engine currently being used – identity for SQL Server and MySQL, sequence for Oracle and PostgreSQL, etc. If you wish to override this, you certainly can:

mapper.BeforeMapClass += (modelInspector, type, classCustomizer) =>

{

    classCustomizer.Id(x =>

    {

        x.Generator(Generators.HighLow);

    });

};

This tells the mapper to use the high-low strategy for all entities.

What if you want to change the default naming of columns and tables? Well, you have two options:

  1. Provide an handler for the BeforeMapClass or BeforeMapProperty events and in it change the name of the physical object:
    mapper.BeforeMapClass += (modelInspector, type, classCustomizer) =>

    {

        classCustomizer.Table(this.GetTableName(type.Name));

    };

    
    

    mapper.BeforeMapProperty += (modelInspector, member, propertyCustomizer) =>

    {

        propertyCustomizer.Column(this.GetColumnName(member.LocalMember.Name));

    };

  2. Provide your own implementation of INamingStrategy to NHibernate:
    public class CustomNamingStrategy : INamingStrategy

    {

        public String ClassToTableName(String className)

        {

            return className;

        }

    
    

        public String ColumnName(String columnName)

        {

            return columnName;

        }

    
    

        public String LogicalColumnName(String columnName, String propertyName)

        {

            return columnName;

        }

    
    

        public String PropertyToColumnName(String propertyName)

        {

            return propertyName;

        }

    
    

        public String PropertyToTableName(String className, String propertyName)

        {

            return propertyName;

        }

    
    

        public String TableName(String tableName)

        {

            return tableName;

        }

    }

    
    

    cfg.SetNamingStrategy(new CustomNamingStrategy());

In general, you can trust NHibernate’s judgement, but if you wish, you can override other aspects of the mapping by providing handlers to the many events of ConventionModelMapper. Say, for example, that you want to exclude (or include) only certain classes from an assembly, you can provide an handler for the IsEntity event:

mapper.IsEntity += (type, @default, assemblyName) =>

{

    return typeof(IEntity).IsAssignableFrom(type);

};

Or you want to configure a collection as a set rather than a bag:

mapper.IsBag += (member, @default) =>

{

    return false;

};


mapper.IsSet += (member, @default) =>

{

    return true;

};

Or even set a collection as not lazy and use inner join fetching:

mapper.BeforeMapSet += (modelInspector, member, propertyCustomizer) =>

{

    propertyCustomizer.Lazy(CollectionLazy.NoLazy);

    propertyCustomizer.Fetch(CollectionFetchMode.Join);

};

 

Lesser-Known NHibernate Features: LINQ Extensions

With NHibernate, you are not bound by the out-of-the box methods that LINQ provides, and their default translations to SQL. I already mentioned that you can add your own extension methods, with minimum work:

public static class StringExtensions

{

    [LinqExtensionMethod("FREETEXT")]

    public static Boolean Freetext(this String propertyName, String value)

    {

        return (propertyName.ToUpper().Contains(value.ToUpper()));

    }

}

For this example, I am creating an extension for the FREETEXT T-SQL function, which is one of the ways by which we can do full-text searching. All it takes is the LinqExtensionMethodAttribute applied to a method, with the name for the database function (can be different from the method name), and that’s it! NHibernate will try to match the parameters:

   1: var result = session.Query<MyEntity>().Where(x => x.Name.Freetext("something") == true).ToList();

Yes… Entity Framework let’s you do this… kind of… only for some functions!

Addendum

As Paulo Morgado (@paulomorgado) pointed out, line “propertyName.ToUpper().Contains(value.ToUpper())” should really be “propertyName.IndexOf(value, StringComparison.IgnoreCase)“, because it avoids string allocations and in general is much better.

Thanks, Paulo! 😉

Lesser-Known NHibernate Features: Serializing Configuration

This isn’t exactly a feature of NHibernate, but it is something that you can do with it and most people isn’t aware of.

If you have a big number of classes and mappings in your domain, adding all of them to a Configuration instance can take some time.

NHibernate allows you to serialize the Configuration instance with all the mappings that it contains so that you can deserialize it later, which can result in reduced startup time. The disadvantage is that if you change any of the mappings, you have to discard the serialized file and build a new one.

Here’s how you can do it:

var serializer = new BinaryFormatter();

 

//serialize

using (var stream = File.OpenWrite("Configuration.bin"))

{

    serializer.Serialize(stream, cfg);

}

 

//deserialize

using (var stream = File.OpenRead("Configuration.bin"))

{

    cfg = serializer.Deserialize(stream) as Configuration)

}

Lesser-Known NHibernate Features: Custom Loggers

Extensible as it is, it’s no wonder that NHibernate also supports injecting custom loggers. The support is twofold:

  • There’s the ILoggerFactory interface, that must be implemented by custom logger factories; this is the responsible for creating actual loggers;
  • Then there’s the IInternalLogger interface, the one that provides the common functions found in most loggers (warn, info, debug, error, fatal, etc).

By default, it uses Log4Net, but it is easy to add our own logger.

So, let’s start by implementing a logger factory – just the skeleton, I leave it to you as an exercise:

public class CustomLoggerFactory : ILoggerFactory

{

    public IInternalLogger LoggerFor(Type type)

    {

        return new CustomLogger(type.FullName);

    }

 

    public IInternalLogger LoggerFor(String keyName)

    {

        return new CustomLogger(keyName);

    }

}

And then the actual logger:

public class CustomLogger : IInternalLogger

{

    public String Key { get; private set; }

 

    public CustomLogger(String key)

    {

        this.Key = key;

    }

 

    public void Debug(Object message, Exception exception)

    {

    }

 

    public void Debug(Object message)

    {

    }

 

    public void DebugFormat(String format, params Object[] args)

    {

    }

 

    public void Error(Object message, Exception exception)

    {

    }

 

    public void Error(Object message)

    {

    }

 

    public void ErrorFormat(String format, params Object[] args)

    {

    }

 

    public void Fatal(Object message, Exception exception)

    {

    }

 

    public void Fatal(Object message)

    {

    }

 

    public void Info(Object message, Exception exception)

    {

    }

 

    public void Info(Object message)

    {

    }

 

    public void InfoFormat(String format, params Object[] args)

    {

    }

 

    public Boolean IsDebugEnabled

    {

        get { return true; }

    }

 

    public Boolean IsErrorEnabled

    {

        get { return true; }

    }

 

    public Boolean IsFatalEnabled

    {

        get { return true; }

    }

 

    public Boolean IsInfoEnabled

    {

        get { return true; }

    }

 

    public Boolean IsWarnEnabled

    {

        get { return true; }

    }

 

    public void Warn(Object message, Exception exception)

    {

    }

 

    public void Warn(Object message)

    {

    }

 

    public void WarnFormat(String format, params Object[] args)

    {

    }

}

Remember, this is just a skeleton, do implement these methods anyway you like.

Now, all that is left is the registration:

LoggerProvider.SetLoggersFactory(new CustomLoggerFactory());

And NHibernate will start logging using your logger!

PS – Issue NH-3776 is an attempt to make using logger factories even simpler and in a similar fashion to other pluggable features.

Lesser-Known NHibernate Features: Mapping By Attributes

Some O/RMs do their mapping based on attributes. LINQ to SQL and Entity Framework are good examples, although Entity Framework also supports mapping by code. I’m not saying this is good or bad – some people think it “pollutes” POCOs, other think it makes them easier to understand -, but, likewise, NHibernate also allows to map entities by attributes, let’s see how.

First, add a reference to the NHibernate.Mapping.Attributes NuGet package:

image

Next, go to your entity and start adding some attributes:

[NHibernate.Mapping.Attributes.Class(Table = "blog", Lazy = true)]

public class Blog

{

    public Blog()

    {

        this.Posts = new List<Post>();

    }


    [NHibernate.Mapping.Attributes.Id(0, Column = "blog_id", Name = "BlogId")]

    [NHibernate.Mapping.Attributes.Generator(1, Class = "hilo")]

    public virtual Int32 BlogId { get; set; }


    [NHibernate.Mapping.Attributes.Property(Name = "Picture", Column = "picture", NotNull = false, TypeType = typeof(ImageUserType), Lazy = true)]

    public virtual Image Picture { get; set; }


    [NHibernate.Mapping.Attributes.Property(Name = "PostCount", Formula = "(SELECT COUNT(1) FROM post WHERE post.blog_id = blog_id)")]

    public virtual Int64 PostCount { get; protected set; }


    [NHibernate.Mapping.Attributes.ManyToOne(0, Column = "user_id", NotNull = true, Lazy = NHibernate.Mapping.Attributes.Laziness.NoProxy, Name = "Owner", Cascade = "save-update")]

    [NHibernate.Mapping.Attributes.Key(1)]

    public virtual User Owner { get; set; }


    [NHibernate.Mapping.Attributes.Property(Name = "Name", Column = "name", NotNull = true, Length = 50)]

    public virtual String Name { get; set; }


    [NHibernate.Mapping.Attributes.Property(Name = "Creation", Column = "creation", NotNull = true)]

    public virtual DateTime Creation { get; set; }


    [NHibernate.Mapping.Attributes.List(0, Name = "Posts", Cascade = "all-delete-orphan", Lazy = NHibernate.Mapping.Attributes.CollectionLazy.True, Inverse = true, Generic = true)]

    [NHibernate.Mapping.Attributes.Key(1, Column = "blog_id", NotNull = true)]

    [NHibernate.Mapping.Attributes.Index(2, Column = "number")]

    [NHibernate.Mapping.Attributes.OneToMany(3, ClassType = typeof(Post))]

    public virtual IList<Post> Posts { get; protected set; }

}

Basically, you will use:

  • ClassAttribute for marking a class as an entity;
  • IdAttribute: for declaring the id property;
  • GeneratorAttribute: for the id generator strategy;
  • PropertyAttribute: a mapped property;
  • BagAttribute/ListAttribute/SetAttribute/ArrayAttribute/etc: kinds of collections;
  • KeyAttribute: the key in a relation;
  • ElementAttribute: an element of an indexed collection;
  • IndexAttribute: the index in an indexed collection;
  • OneToManyAttribute/ManyToOneAttribute/OneToOneAttribute/ManyToManyAttribute: an endpoint property.

All these attributes live in the NHibernate.Mapping.Attributes namespace and are named very closely to the equivalent HBM.XML elements, which means they must be ordered (notice the first number on some attributes). Now, in order to actually make sense of these attributes, you need to:

var cfg = new Configuration();

var serializer = new HbmSerializer() { Validate = true };


using (var stream = serializer.Serialize(assemblyContainingEntitiesWithAttributes))

{

    cfg.AddInputStream(stream);

}

And that’s all it takes! Have fun! Winking smile

Entity Framework Extensibility Index

Updated on March 10th.

Here you will find a list of all my posts on Entity Framework extensibility.

Freetext Extension in Entity Framework Code First

I posted before a solution for adding custom SQL functions to Entity Framework Code First as extension methods. This time I am going to show how we can do something similar for the FREETEXT function of SQL Server. Please note that this example will only work if you have the Fulltext Search component installed and your table is indexed.

OK, so we want to have an extension method like this:

[DbFunction("CodeFirstDatabaseSchema", "FREETEXT")]

public static Boolean Freetext(this String column, String value)

{

    return column.Contains(value);

}

In order for Entity Framework to recognize it, we need to write our own convention, this is because Entity Framework only recognizes out of the box a number of SQL Server built-in functions. We can write one as this:

public class FreetextConvention : IStoreModelConvention<EdmModel>

{

    public static readonly FreetextConvention Instance = new FreetextConvention();


    public void Apply(EdmModel item, DbModel model)

    {

        var valueParameter = FunctionParameter.Create("column", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);

        var formatParameter = FunctionParameter.Create("value", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);

        var returnValue = FunctionParameter.Create("result", this.GetStorePrimitiveType(model, PrimitiveTypeKind.Boolean), ParameterMode.ReturnValue);


        var function = this.CreateAndAddFunction(item, "FREETEXT", new[] { valueParameter, formatParameter }, new[] { returnValue });

    }


    protected EdmFunction CreateAndAddFunction(EdmModel item, String name, IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues)

    {

        var payload = new EdmFunctionPayload { StoreFunctionName = name, Parameters = parameters, ReturnParameters = returnValues, Schema = this.GetDefaultSchema(item), IsBuiltIn = true };

        var function = EdmFunction.Create(name, this.GetDefaultNamespace(item), item.DataSpace, payload, null);


        item.AddItem(function);


        return (function);

    }


    protected EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)

    {

        return (model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType);

    }


    protected String GetDefaultNamespace(EdmModel layerModel)

    {

        return (layerModel.GlobalItems.OfType<EdmType>().Select(t => t.NamespaceName).Distinct().Single());

    }


    protected String GetDefaultSchema(EdmModel layerModel)

    {

        return (layerModel.Container.EntitySets.Select(s => s.Schema).Distinct().SingleOrDefault());

    }

}

This registers a FREETEXT function with two string parameters and returning a boolean. All is fine, we add it to the DbContext in OnModelCreating:

modelBuilder.Conventions.Add(FreetextConvention.Instance);

You might have noticed the usage of a Instance static field, this is because, since the FreetextConvention class is stateless, there’s no point in creating many of them, we can just use the same instance.

Now, if we issue a LINQ query as:

var customers = ctx.Customers.Where(x => x.Name.Freetext("ricardo")).ToList();

It will fail miserably, complaining about this SQL fragment:

WHERE ((FREETEXT(name, N'ricardo') = 1)

The “= 1” part is here because the function is prototyped as boolean, which maps to SQL Server’s BIT data type, and the value for true is 1. Apparently, SQL Server does not support comparisons of some functions with 1; but if we run it as:

WHERE ((FREETEXT(name, N'ricardo'))

without the explicit comparison, it works perfectly. So, all we have to do is get rid of “= 1”. Fortunately, Entity Framework, as of version 6, offers some very nice extensibility points. There are at least two ways by which we can achieve this:

  • By intercepting the command tree;
  • By intercepting the raw SQL.

Here we will use option #2 and leave command trees for another post.

We need to identity something with a format of “FREETEXT(something) = 1”. We can do it using a regular expression, and the interception of the SQL command can be achieved by implementing IDbCommandInterceptor (no reference documentation yet, but I have reported it and it will soon be fixed, hopefully) and registering one such instance in the DbInterception (same) static class. An IDbCommandInterceptor implementation might look like this:

public class FreetextInterceptor : IDbCommandInterceptor

{

    public static readonly FreetextInterceptor Instance = new FreetextInterceptor();


    private static readonly Regex FreetextRegex = new Regex(@"FREETEXT\(([^)]+\))\) = 1");


    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)

    {

    }


    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)

    {

    }


    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)

    {

    }


    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)

    {

        var matches = FreetextRegex.Matches(command.CommandText);


        if (matches.Count > 0)

        {

            command.CommandText = FreetextRegex.Replace(command.CommandText, "FREETEXT($1)");

        }

    }


    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<Object> interceptionContext)

    {

    }


    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<Object> interceptionContext)

    {

    }

}

You can see that the only method we’re interested in is ReaderExecuting (again, no documentation available), with is the one that will be called just before a SQL SELECT query is sent to the database. In here we analyze the CommandText property of the DbCommand and get rid of the “= 1” clause, using a regular expression. Finally, we need to register the interceptor before we issue the query, maybe in the static constructor of our DbContext:

DbInterception.Add(FreetextInterceptor.Instance);

And now we can finally execute our query:

var customers = ctx.Customers.Where(x => x.Name.Freetext("ricardo")).ToList();

And that’s it. Don’t forget that in order for this to work, you need to enable Full Text Search.