Posts Tagged ‘Entity Framework’
There’s a lot of new stuff coming with the Entity Framework June 2011 CTP – enums and spatial types and … you can find it all around blogosphere. And for sure, I’ll cover my finding as I’ll dive into it. But there’s one, one could call it very minor, improvement, that was in, my opinion, pain in the ass.
Previously, the designer settings, like positions of lines for associations, sizes of entities and even zoom level were stored directly in EDMX file. Hence possibly creating changes in it even if you only read it. But the new CTP solves it. The designer’s content is now in a separate file. So if you’re about to commit to VCS you can much better see what was really changed – whether then model itself or only the designer (and you can ignore designer changes if you want).
Nice isn’t it? Pity that this “bug” made it to the first release. 
I’m proud to announce new version of ADO.NET provider for Firebird – 2.6.5. It’s half maintenance release, half new features.
You can find all bug fixes in tracker.
The new features include and improvements:
* Support for Trace API in Firebird 2.5.
* Improvements in SQL generation for Entity Framework.
* Support for commands logging .
* Slightly faster command execution of big queries.
* And a lot of small code improvement making it more stable…
You can download it at http://sourceforge.net/projects/firebird/files/firebird-net-provider/2.6.5/ or http://www.firebirdsql.org/en/net-provider/.
Hope you’ll enjoy the release.
In a lot of articles the Entity Framework’s 4.1 Code First is described as third option to the already available so called Database First and Model First paths. But I’m not sure, it’s really a third distinct path. At least it’s not so clear.
Let’s describe what Database First means. You simply create a model by reverse engineering your current database. Then you can tweak the conceptual part and do the development. You’re not limited to work with generated classes, POCOs work too. Your model is described as XML file(s) (often in one file with EDMX extension).
The Model First goes the other way. You start with blank designer surface and at some time generate the SQL script to create database based on what’s on the surface. Of course you can use POCOs here as well. The model is stored in XML file, same as in Database First scenario.
But what the Code First does? Focusing only on important differences, it only allows you to save model as code (in any language being able to produce MSIL). Really. The only difference is the model representation, to be precise the mapping and kind of SSDL.
There’s no explicit conceptual part. You already have it. It is the objects you created – either generated or manually written both POCOs or derived from EntityObject class. The mapping and also the store model is described with your code. EntityTypeConfiguration, ComplexTypeConfiguration etc. classes directly or indirectly written. Also part of mapping could be inferred if you’re using conventions. And ultimately you can point this to an existing database or let the Entity Framework create it for you (or the SQL script). That’s when the database initializers (and providers) come to play.
Julia Lerman created a nice decision chart if you’re not sure what way to go. But in my eyes the only decision to make is whether you want to have everything in .NET code or in XML (and in fact, in runtime both approaches will create same in-memory representation).
Existing database & code ⇒ Code First
Existing database & XML ⇒ Database First
New database & code ⇒ Code First
New database & XML ⇒ Model First
See. So I think it’s more 2×2 options (and Code First being able to do two tasks) and we might call it: Code Database First, Code Model First, EDMX Database First and EDMX Model First or Database First with Code, Model First with Code, Database First with EDMX and Model First with EDMX. 
I was speaking at Gopas Teched few days ago and there was a good question from audience about how to get the SQL script the DbContext is using to create database.
I never thought about it as I always create database in ER tool as it provides more features (like triggers, stored procedures etc.). But I remembered I implemented this method in .NET provider for Firebird. So it has to be somewhere.
The method is called CreateDatabaseScript and it’s on ObjectContext. So it was easy to expose it directly from DbContext, because it has ObjectContext under the hood (you can access it via IObjectContextAdapter).
public static string CreateDatabaseScript(this DbContext context)
{
return ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();
}
Hope the questioner will find this blog post.
Even if you remove all conventions when using Code First you might get errors from Entity Framework about not being able to properly map some items. The reason is type mapper. In RC (and very probably in RTM as well) it’s not implemented as convention, hence always kicks in.
In this case the Ignore method comes into play. For instance I have in my code property:
public CultureInfo Locale
{
get { ... }
set { ... }
}
and I’m not mapping it at any place. But Entity Framework will still complain about pieces of CultureInfo not being properly mapped. But in EntityTypeConfiguration can make Entity Framework to ignore it. For example:
class FooBarConfiguration : EntityTypeConfiguration<FooBar>
{
public FooBarConfiguration()
{
// ...
this.Ignore(x => x.Locale);
this.Map(...);
}
}
At first I was confused, but after quick email exchange with EF team the “issue” was clear.
Convention-over-configuration is great, at least for a quick start, in my opinion. But if you’re like me and you want everything under your control, you may want to remove all (at least those you can remove via code).
Because now there’s no list available (based on RC version, but very probably it’ll be same in RTM) as it was in i.e. CTP5 you need to kind of get all items implementing IConvention interface. And because I want my code work no matter what will be added or removed in next versions, I’m not going to hardcode these. Couple of lines with reflection and we’re done.
MethodInfo method;
method = typeof(ConventionsConfiguration).GetMethod("Remove");
foreach (var convention in Assembly.GetCallingAssembly()
.GetTypes()
.Where(t => t.Namespace == "System.Data.Entity.ModelConfiguration.Conventions" || t.Namespace == "System.Data.Entity.Infrastructure")
.Where(t => t.GetInterface("IConvention", false) != null && !t.IsInterface && !t.IsAbstract))
{
method.MakeGenericMethod(convention).Invoke(modelBuilder.Conventions, null);
}
I’m simply looking into System.Data.Entity.ModelConfiguration.Conventions namespace, where all the “I-do-the-mapping-for-you” conventions are and also System.Data.Entity.Infrastructure namespace, where the default database creation etc. stuff lives. The code is in overridden OnModelCreating method.
Now you can explore what one must do to create proper mapping by hand.
I do love Entity Framework. And you probably know it from the amount of weird ways to do some things published on this blog. And so I do love Code First. It’s like having all the power absolutely under control (or at least there’s a possibility to have it). And I also do love Firebird. It’s a great database engine. And I admit, it has strong as well weak points. But every engine does.
So there’s no wonder I’m using Entity Framework’s 4.1 (currently in CTP stage) Code First with Firebird. If you’re a bit lazy and you’re specifying only required minimum on information to run the mapping, you might quickly face one problem.
Long story. If you generated your model from database, the SSDL contained a lot of information about database structure. Especially lengths of (var)char fields. And these information were then used by provider for Firebird to create proper queries. But with Code First it’s bit boring to specify lengths for string fields moreover, when it works without it.
But the Firebird’s API has some limitations (especially around sizes of stuff) and by default (right now – CTP) the (var)char fields are 4000 characters long. The amount of (var)char parameters in code is limited (depends on few variables like charset, hence I’m not putting here exact number) and with UTF8 (multi-byte charset) it’s even more limited. And UTF8 is option #1 in .NET world.
All this together, couple of (var)char fields without length specified and you could start seeing Implementation limit exceeded and block size exceeds implementation restriction etc. The first place where you’ll see it is inserting, because there you have all the fields.
The solution is easy. Specify lengths explicitly and hope for the best. If the table is very wide (aka it doesn’t work), you could always split the table in more (yes, not great, but …).
Entity Framework v4 CTP5 added some new nice features since CTP4, for example the DbSet<T>.Local property I was blogging about or Validation. But, sadly, there’s something missing, mainly due to huge refactoring.
I hit the wall with one in particular. It’s entity splitting together with TPH inheritance. If you try to map it, you’ll get Entity splitting cannot be specified for type '<entity type>' since it is part of an inheritance hierarchy.. Bummer. So one of the projects I’m working on now, is stuck in CTP4.
On the other hand, the good news (from reliable source 
) is, that in RTM this will be working fine.
Currently latest CTP for Entity Framework, CTP5, contains one new property on DbSet<T>. It’s called Local and it’s very useful when you wanna work with objects you have already in memory. So it’s good for queries without hitting the database, like databinding.
You can create layer that loads objects your application needs, or you think it might need. And then you use only .Local to access that data, still full LINQ support etc. This may help to lower number of queries you’re sending to database. The property is of type ObservableCollection<T>, thus the databinding is super easy.
The method isn’t something new. In fact it’s exposing in a friendly was what’s already possible – i.e. Useful Find method on DbSet and “Local” Queries 2nd edition. It is using ObjectStateManager to find all Unchanged, Added and Modified entities, exactly the ones you’re probably interested in.
Let’s see some example:
class Program
{
static void Main(string[] args)
{
/*
* I have 3 rows in FOOBAR table in database.
*/
using (MyContext c = new MyContext())
{
// return's 2 rows and hits database
var data1 = c.Set<FooBar>().OrderBy(x => x.ID).Take(2).ToArray();
// return's 2 rows without hitting database
var data2 = c.Set<FooBar>().Local.ToArray();
// return's 1 row and hits database
var data3 = c.Set<FooBar>().OrderBy(x => x.ID).Skip(2).Take(1).ToArray();
// return's 3 rows without hitting database
var data4 = c.Set<FooBar>().Local.ToArray();
}
}
}
class MyContext : DbContext
{
public MyContext()
: base(new FbConnection("database=localhost:rrr.fdb;user=sysdba;password=masterkey"), true)
{ }
protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<FooBar>().HasKey(x => x.ID);
modelBuilder.Entity<FooBar>().Property(x => x.ID).HasColumnName("ID");
modelBuilder.Entity<FooBar>().Property(x => x.Something).HasColumnName("S");
modelBuilder.Entity<FooBar>().Map(m => m.ToTable("FOOBAR"));
}
}
class FooBar
{
public int ID { get; set; }
public string Something { get; set; }
}
All important stuff about behavior is in comments. As you see, it acts as kind of local cache of objects, that are (mostly) reasonable to work with.
As always, nothing huge, but nice, handy.
PS: Did you noticed, I’m using Firebird?
Firebird .NET provider team is proud to announce next version of ADO.NET provider for Firebird – 2.6.0.
This version contains various bug fixes as well as new features and improvements. And of course support for some of new Firebird server features. Highlights:
- .NET 4 Client Profile support
- Entity Framework 4 support
- Command cancellation support
- Support for UTF8 filenames
All changes can be found at tracker.firebirdsql.org.
You can download it at http://sourceforge.net/projects/firebird/files/firebird-net-provider/2.6.0/ or http://www.firebirdsql.org/index.php?op=files&id=netprovider.
Thanks to all who helped improving and hunting issues.
If you’re specifying mapping in Code First in Entity Framework you’re essentially describing it with like this (assuming using EntityConfiguration class).
this.Something(x => x.Foo).Bar();
This is nice, but if the property you wanna to use is either private or protected you are hitting wall as you can’t write such expression. I faced the same problem today. The easiest approach is to have the configuration class nested to entity itself. But that’s not a clean (or may not be doable) and I do want clean code.
Because I know, Entity Framework can use these properties – if you’re using designer it’s just setting some fields in Properties window. So I concluded, that the only problem is to how to push it into Code First. My focus was primarily for MapSingleType method (but it’s doable for i.e. Property method as well).
With the method you can write two types of mapping, one using EntityMap class directly.
this.MapSingleType(x =>
EntityMap.Row(
EntityMap.Column(x.Foo, "FooColumn"),
EntityMap.Column(x.Bar, "BarColumn")))
.ToTable(new StoreTableName("Baz", "dbo"));
Or one using anonymous type.
this.MapSingleType(x => new
{
FooColumn = x.Foo,
BarColumn = x.Bar
})
.ToTable(new StoreTableName("Baz", "dbo"));
With the starting point set I decided the easiest way to specify private or protected properties will be using string. The only task is to create the Expression that’s else generated by compiler. After some juggling with the trees I created this extension method.
public struct ColumnPropertyMapping
{
public string Column { get; set; }
public string Property { get; set; }
public ColumnPropertyMapping(string column, string property)
: this()
{
this.Column = column;
this.Property = property;
}
}
public static EntityMap MapSingleType<TEntity>(this EntityConfiguration<TEntity> configuration, Expression<Func<TEntity, object>> initialMapping, params ColumnPropertyMapping[] additionalMappings)
where TEntity : class
{
if (additionalMappings == null)
throw new ArgumentNullException("additionalMappings");
List<Expression> newParameters = new List<Expression>();
var entity = initialMapping.Parameters[0];
Func<ColumnPropertyMapping, MethodCallExpression> makeColumnCall =
m =>
Expression.Call(
typeof(EntityMap),
"Column",
null,
Expression.Convert(
Expression.MakeMemberAccess(entity, typeof(TEntity).GetMember(m.Property, BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).First()),
typeof(object)),
Expression.Constant(m.Column));
var callExpression = (initialMapping.Body as MethodCallExpression);
var newExpression = (initialMapping.Body as NewExpression);
if (callExpression != null)
{
newParameters.AddRange((callExpression.Arguments[0] as NewArrayExpression).Expressions);
}
else if (newExpression != null)
{
newParameters.AddRange(newExpression.Arguments.Select((e, i) => new ColumnPropertyMapping(newExpression.Members[i].Name, (e as MemberExpression).Member.Name)).Select(x => makeColumnCall(x)));
}
else
{
throw new ArgumentException("initialMapping");
}
newParameters.AddRange(additionalMappings.Select(x => makeColumnCall(x)));
var finalMapping = Expression.Lambda<Func<TEntity, object>>(
Expression.Call(
typeof(EntityMap),
"Row",
null,
Expression.NewArrayInit(
typeof(EntityMapColumn),
newParameters)),
entity);
return configuration.MapSingleType(finalMapping);
}
It’s method with similar signature as the original one, but taking extra collection of ColumnPropertyMapping, my helper objects to represent the mapping as strings. I take the input – EntityMap or anonymous object – peck up the important pieces and recreate the expression with added properties. I’m resulting to tree with EntityMap, as it looked easier to create. So now you can create the mapping also for non-public properties (and also dynamic mapping is easier).
this.MapSingleType(x =>
EntityMap.Row(
EntityMap.Column(x.Foo, "FooColumn"),
EntityMap.Column(x.Bar, "BarColumn")),
new ColumnPropertyMapping("SomeColumn", "ImNotPublic"))
.ToTable(new StoreTableName("Baz", "dbo"));
// or
this.MapSingleType(x => new
{
FooColumn = x.Foo,
BarColumn = x.Bar
},
new ColumnPropertyMapping("SomeColumn", "ImNotPublic"))
.ToTable(new StoreTableName("Baz", "dbo"));
Enjoy, if you need it. I hope the CTP5 will address this “scenario”, thus I’ll not be forced to write it for other methods. And if not, stay tuned, I’ll definitely post it.
I recently discovered nice feature of NHibernate. It’s called MultiQuery (but the name doesn’t matter). The idea behind is simple. Instead of sending multiple queries one by one and melting performance of your application in network latency, send all in one batch.
I read couple of articles about it. Later something in my head started to working and I had an idea about trying to do it in Entity Framework. I had a basic concept in my head in couple of minutes and I told myself I’ll try to do it, but I’ll not invest too much time into it. Just quick’n'dirty brain exercise for Saturday (alike Bart de Smet‘s Crazy Sundays).
The concept was simple. Record couple of ObjectQuery objects, get commands out of these, create one huge batch, re-wire parameters (more about that later) and get results.
public class MultiQuery
{
struct QueryRecord
{
public ObjectQuery Query { get; set; }
public Type Type { get; set; }
public static QueryRecord Create<T>(ObjectQuery<T> query)
{
return new QueryRecord() { Query = query, Type = typeof(T) };
}
}
#region Fields
ObjectContext _context;
List<QueryRecord> _queries;
#endregion
#region Constructors
public MultiQuery(ObjectContext context)
{
_queries = new List<QueryRecord>();
_context = context;
}
#endregion
#region Public Methods
public MultiQuery Add<T>(ObjectQuery<T> query)
{
if (query == null)
throw new ArgumentNullException("query");
_queries.Add(QueryRecord.Create(query));
return this;
}
public MultiQuery Add<T>(IQueryable<T> query)
{
return this.Add(query as ObjectQuery<T>);
}
public IEnumerable<ObjectResult> Execute()
{
IDbConnection storeConnection = ((EntityConnection)_context.Connection).StoreConnection;
using (IDbCommand cmd = storeConnection.CreateCommand())
{
IDataParameterCollection parameters = cmd.Parameters;
cmd.CommandText = CreateCommand(_queries.Select(q => q.Query), cmd.CreateParameter, ref parameters);
bool shouldClose = (_context.Connection.State == ConnectionState.Closed);
try
{
storeConnection.Open();
using (IDataReader reader = cmd.ExecuteReader())
{
int cnt = 0;
do
{
yield return _context.Translate(_queries[cnt].Type, reader);
cnt++;
} while (reader.NextResult());
}
}
finally
{
if (shouldClose)
storeConnection.Close();
}
}
}
#endregion
#region Private Methods
string CreateCommand(IEnumerable<ObjectQuery> queries, Func<IDataParameter> parameterCreator, ref IDataParameterCollection parameters)
{
List<string> commands = new List<string>();
int cnt = 0;
foreach (var q in _queries.Select(q => q.Query))
{
string query = q.ToTraceString();
foreach (var p in q.Parameters)
{
IDataParameter parameter = parameterCreator();
parameter.ParameterName = string.Format("@p{0}", cnt++);
parameter.Value = p.Value;
parameters.Add(parameter);
// Not good. Better (and still easy) idea?
query = query.Replace(string.Format("@{0}", p.Name), parameter.ParameterName);
}
commands.Add(query);
}
return string.Join(";" + Environment.NewLine, commands);
}
#endregion
}
static class MultiQueryExt
{
internal static ObjectResult Translate(this ObjectContext context, Type type, IDataReader reader)
{
// ObjectResult<TElement> Translate<TElement>(DbDataReader reader)
object result =
context
.GetType()
.GetMethod("Translate", new[] { typeof(DbDataReader) })
.MakeGenericMethod(type)
.Invoke(context, new object[] { reader });
return (ObjectResult)result;
}
}
I’m here fully utilizing new Translate method in Entity Framework 4 (for v1 similar method is available in EFExtensions). The rest is done using pure ADO.NET. It’s worth noting, that this code, same as in NHibernate, works only if the database and the underlying provider supports processing more queries in one command (i.e. Microsoft SQL Server does, but Firebird does not).
Also small notice to parameters. I’m doing simple replace and that’s dumb. It may fail and produce wrong results, but in very rare cases. So you should test thoroughly. The case when it produces wrong results is, when you write query in where you use directly (not as a variable etc.) string that is same as parameter name (i.e. p__linq__<number> for SqlClient or p<number> for FirebirdClient). As you are in control of these strings you can change the code to use a variable, for instance.
A lot of “fetching” methods in Entity Framework supports also MergeOption. Adding overload for Execute I’m leaving as exercise for readers. Likewise for the Entity SQL queries.
It may came as a shock but Firebird does not have direct support for neither bools nor guids. On the other hand people around Firebird are smart and came with more or less standard solutions for both. The bool is easy, just use number with constraint to 0 or 1. For guid we (ab)use special character set available in engine. It’s called OCTETS and it’s exactly what you think it is. Just a bunch of binary data, without any other interpretation from engine. That means CHAR(16) and the above character set is a perfect match for storing (not only) guids.
On the other side, where .NET Framework and Entity Framework lives, the bool and guid datatypes are core part of both frameworks. This created a small mismatch or better to say inconvenience. It wasn’t showstopper but working with (or actually without) it wasn’t pleasure either.
But this is over, since today, I used similar trick we used for “identity” columns and added two new special keyword (if I can call it like that). #BOOL# and #GUID#. When you use these (we’re looking for these in whole comment, so you can place it anywhere you want), your model will contain properties with accordant types. (Note, we’re not doing any checks whether your underlying datatype is compatible, it’s up to you.) Similarly the internals of Entity Framework support were improved to handle these changes correctly (as well as Model First support).
If you wanna try it, grab it from SVN or weekly builds and enjoy. And report any problems you encounter, of course.
The MetadataWorkspace contains a lot of useful information. Recently I was facing a challenge to get information about TPH (table per hierarchy) inheritance conditions for particular type. Sure, it’s in EDMX file and/or in MSL file. So you can parse the XML and get the info. I was on the other hand more interested getting the info from MetadataWorkspace, partially as a good “brain training” side project.
Sadly the information about the mapping is very limited. Most interesting parts are not public, thus you’re forced to use reflection. So it’s a lot back and forth with, in my case, QuickWatch window. It helps a little to be familiar with MSL file structure.
static object GetNonPublicPropertyValue(this object o, string propertyName)
{
return o.GetType()
.GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.Instance)
.GetValue(o, null);
}
public static IEnumerable<KeyValuePair<string, object>> GetMappingConditions<T>(this ObjectContext context)
where T : class
{
string typeToSearch = typeof(T).Name;
var mapping = context.MetadataWorkspace.GetItemCollection(DataSpace.CSSpace).First();
return ((IEnumerable<object>)mapping.GetNonPublicPropertyValue("EntitySetMaps"))
.SelectMany(entitySetMap => (IEnumerable<object>)entitySetMap.GetNonPublicPropertyValue("TypeMappings"))
.Where(typeMapping =>
((IEnumerable<dynamic>)typeMapping.GetNonPublicPropertyValue("IsOfTypes")).Any(type => type.Name == typeToSearch)
||
((IEnumerable<dynamic>)typeMapping.GetNonPublicPropertyValue("Types")).Any(type => type.Name == typeToSearch))
.SelectMany(typeMapping => (IEnumerable<object>)typeMapping.GetNonPublicPropertyValue("MappingFragments"))
.SelectMany(mappingFragment => (IEnumerable<object>)mappingFragment.GetNonPublicPropertyValue("AllProperties"))
.Where(mappingFragment => mappingFragment.GetType().Name == "StorageConditionPropertyMapping")
.Select(condition =>
{
bool? isNull = (bool?)condition.GetNonPublicPropertyValue("IsNull");
string value = (string)condition.GetNonPublicPropertyValue("Value");
return new KeyValuePair<string, object>((string)((dynamic)condition.GetNonPublicPropertyValue("ColumnProperty")).Name, (isNull.HasValue ? (object)isNull.Value : (object)value));
});
}
Because the code is heavily using reflection and non public members, it’s possible it’ll not work other/future versions of Entity Framework. I tested it with current version, version 4.
It’s written in a compact way. If you want to further dig into partial results, I recommend to split it into foreach loops and do small steps. That’s in fact how I started and was incrementally discovering the information available at given level.
As all the data are not public, I’ll not describe how and why it is as it is. I did it using trial and error process. Maybe there’s other/simpler path. Feel free to use comments if you find one.
There’s a lot of content for latest CTP, CTP4, for Entity Framework “new features”. It’s mainly focused on Code First stuff, that fills the triad with Database First and Model First. I like Code First but what I also like is maintainable code. Hence I was more trying not to use convention-over-configuration, in opinion good for only small projects, and focus on specifying everything the way I want it to be, especially with my database structure (yes, I’m data consistency and storage freak).
Let’s model some kind of simple library and try to use there couple of Entity Framework’s features. And to make things worse, try to do it with ADO.NET provider for Firebird.
Good news is I succeeded. My database structure was my first and non-touchable object (to be honest the first was the idea what to create and how to represent it in database) together with the idea of objects.
Here’s the script for Firebird database (Did you noticed we’re fully Entity Framework 4 compatible?). See the quoted column names? Yes, not nice, but later in mapping in C# it looks nicer. And I mixed the mapping with one for MS SQL Server (see below).
RECREATE TABLE Authors (
"id" INT NOT NULL,
"FirstName" VARCHAR(255) NOT NULL,
"LastName" VARCHAR(255) NOT NULL,
CONSTRAINT PK_Authors PRIMARY KEY ("id")
);
RECREATE TABLE Books (
"id" INT NOT NULL,
"Discriminator" char(2) NOT NULL,
"Title" VARCHAR(1000) NOT NULL,
"Published" TIMESTAMP NOT NULL,
"ID_Author" INT NOT NULL,
"IssuesPerYear" SMALLINT,
"Price" DECIMAL(9,0),
CONSTRAINT PK_Books PRIMARY KEY ("id")
);
RECREATE TABLE Languages (
"id" INT NOT NULL,
"LanguageName" VARCHAR(100) NOT NULL,
"LanguageAbbrevation" CHAR(3),
CONSTRAINT PK_Languages PRIMARY KEY ("id")
);
RECREATE TABLE Translators (
"id" INT NOT NULL,
CONSTRAINT PK_Translators PRIMARY KEY ("id")
);
RECREATE TABLE Translators_Languages (
"ID_Language" INT NOT NULL,
"ID_Translator" INT NOT NULL,
CONSTRAINT PK_Translators_Languages PRIMARY KEY ("ID_Language", "ID_Translator")
);
ALTER TABLE Books ADD CONSTRAINT FK_Book_Author FOREIGN KEY ("ID_Author")
REFERENCES Authors("id")
ON DELETE CASCADE
;
ALTER TABLE Translators_Languages ADD CONSTRAINT FK_TL_Languages FOREIGN KEY ("ID_Language")
REFERENCES Languages("id")
ON DELETE NO ACTION
;
ALTER TABLE Translators_Languages ADD CONSTRAINT FK_TL_Translators FOREIGN KEY ("ID_Translator")
REFERENCES Translators("id")
ON DELETE NO ACTION
;
ALTER TABLE Translators ADD CONSTRAINT FK_TypeConstraint FOREIGN KEY ("id")
REFERENCES Authors("id")
ON DELETE NO ACTION
;
So it’s time to create mapping right? Nope. Now I’ll switch my brain from ER thinking into OO thinking mode. Here’s the world of entities (sure I made some adjustments to fit some Entity Framework features, like Complex Types):
public abstract class Book
{
public int ID { get; protected set; }
public string Title { get; set; }
public DateTime Published { get; set; }
public Author Author { get; set; }
public int AuthorID { get; set; }
}
public class RealBook : Book
{
public decimal Price { get; set; }
}
public class Magazine : Book
{
public short IssuesPerYear { get; set; }
}
public class Author
{
public int ID { get; protected set; }
public Name FullName { get; set; }
public ICollection<Book> Books { get; set; }
public Author()
{
this.Books = new List<Book>();
}
}
public class Translator : Author
{
public ICollection<Language> Languages { get; set; }
public Translator()
{
this.Languages = new List<Language>();
}
}
public class Language
{
public int ID { get; protected set; }
public string LanguageName { get; set; }
public string LanguageAbbrevation { get; set; }
}
#region Complex Types
public class Name
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
#endregion
As you see I’m using pure POCOs.
So far we haven’t touched any Entity Framework related stuff. So it’s time to create our context and all DAL related stuff. Mine is very simple and exposes only few properties and methods, just to keep it simple and focus on the aim.
public class LibraryContext : DbContext
{
public LibraryContext(DbConnection connection)
: base(connection)
{
this.ObjectContext.ContextOptions.LazyLoadingEnabled = false;
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Configurations.Add(new BookConfiguration());
modelBuilder.Configurations.Add(new AuthorConfiguration());
modelBuilder.Configurations.Add(new TranslatorConfiguration());
modelBuilder.Configurations.Add(new LanguageConfiguration());
modelBuilder.Configurations.Add(new NameConfiguration());
}
public string CreateDatabaseScript()
{
return this.ObjectContext.CreateDatabaseScript();
}
public IDbSet<Book> Books
{
get { return this.Set<Book>(); }
}
public IDbSet<Author> Authors
{
get { return this.Set<Author>(); }
}
}
public class DoNothingWithMyDatabase<TContext> : IDatabaseInitializer<TContext>
where TContext : DbContext
{
public void InitializeDatabase(TContext context)
{ }
}
Pretty simple, isn’t it. I’m using new stripped down objects DbContext (← ObjectContext) and IDbSet/DbSet (← IObjectSet/ObjectSet).
You may also notice, I’ve created object which implements IDatabaseInitializer. That’s because, by default, Entity Framework will try to create database for you and check whether your database matches model (CreateDatabaseOnlyIfNotExists). I‘m controlling my database. You have to add somewhere into your application before doing anything with the context call to SetInitializer:
Database.SetInitializer(new DoNothingWithMyDatabase<LibraryContext>());
And finally we’re ready to dive into the mapping. Again, you can type the mapping directly into overridden OnModelCreating, useful if you have only couple of diversions from default conventions. I created configuration classes where I specified as much as I want. And I need (almost) everything to be nailed down (remember, data consistency and storage freak ) as I don’t want to be surprised when something in database or in entities changes and application will start behave weird.
class BookConfiguration : EntityConfiguration<Book>
{
public BookConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.Property(x => x.Title).IsRequired().IsVariableLength().HasMaxLength(1000).IsUnicode();
this.Property(x => x.Published);
this.HasRequired(x => x.Author).WithMany(a => a.Books).WillCascadeOnDelete().HasConstraint((b, a) => b.AuthorID == a.ID);
this.MapHierarchy()
.Case<Book>(x => new
{
id = x.ID,
Title = x.Title,
Published = x.Published,
ID_Author = x.AuthorID,
})
.Case<RealBook>(x => new
{
Discriminator = "B",
Price = x.Price,
})
.Case<Magazine>(x => new
{
Discriminator = "M",
IssuesPerYear = x.IssuesPerYear,
})
#if FB
.ToTable("BOOKS");
#else
.ToTable("Books");
#endif
}
}
class AuthorConfiguration : EntityConfiguration<Author>
{
public AuthorConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.HasMany(x => x.Books);
this.MapHierarchy(x => new
{
id = x.ID,
FirstName = x.FullName.FirstName,
LastName = x.FullName.LastName,
})
#if FB
.ToTable("AUTHORS");
#else
.ToTable("Authors");
#endif
this.MapHierarchy().Case<Translator>(x => new
{
id = x.ID,
})
#if FB
.ToTable("TRANSLATORS");
#else
.ToTable("Translators");
#endif
}
}
class TranslatorConfiguration : EntityConfiguration<Translator>
{
public TranslatorConfiguration()
{
this.HasMany(x => x.Languages).WithMany()
#if FB
.Map("TRANSLATORS_LANGUAGES",
#else
.Map("Translators_Languages",
#endif
(t, l) => new
{
ID_Translator = t.ID,
ID_Language = l.ID,
});
}
}
class LanguageConfiguration : EntityConfiguration<Language>
{
public LanguageConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.Property(x => x.LanguageName).IsRequired().IsVariableLength().HasMaxLength(100).IsUnicode();
this.Property(x => x.LanguageAbbrevation).IsFixedLength().HasMaxLength(3).IsUnicode();
this.MapSingleType(x => new
{
id = x.ID,
LanguageName = x.LanguageName,
LanguageAbbrevation = x.LanguageAbbrevation
})
#if FB
.ToTable("LANGUAGES");
#else
.ToTable("Languages");
#endif
}
}
class NameConfiguration : ComplexTypeConfiguration<Name>
{
public NameConfiguration()
{
this.Property(x => x.FirstName).IsRequired().IsVariableLength().HasMaxLength(255).IsUnicode();
this.Property(x => x.LastName).IsRequired().IsVariableLength().HasMaxLength(255).IsUnicode();
}
}
I don’t know whether it’s worth to describe the lines. Should be understandable if you know how the entities and database look like. Just maybe small notice. Besides Complex Types we used two most common inheritance mapping scenarions – TPH aka Table Per Hierarchy for Books and TPT aka Table Per Type for Authors (there’s also TPC (Table Per Concrete Type)). However if you have questions feel free to use comments, if I’ll know answer I’ll be happy to reply.
And finally some really simple application to test the result:
If you define #define FB it’ll use Firebird database else MS SQL Server.
Database.SetInitializer(new DoNothingWithMyDatabase<LibraryContext>());
Action<LibraryContext> doSomething = (context) =>
{
Console.WriteLine(context.CreateDatabaseScript());
var query = context.Books
.Select(b => new
{
BookName = b.Title,
AuthorName = b.Author.FullName.LastName + ", " + b.Author.FullName.FirstName
})
.OrderBy(x => x.BookName);
Console.WriteLine((query as ObjectQuery).ToTraceString());
var data = query.ToArray();
foreach (var item in data)
{
Console.WriteLine("Book {0} written by {1}.", item.BookName, item.AuthorName);
}
};
#if FB
using (LibraryContext context = new LibraryContext(new FbConnection(@"database=localhost:ctp4;username=sysdba;password=masterkey;pooling=true;")))
{
doSomething(context);
}
#else
using (LibraryContext context = new LibraryContext(new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ctp4;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True")))
{
doSomething(context);
}
#endif
I’m glad I was able to figure out how to map everything without touching database structure (although it’s pretty straightforward) or letting the default rules to kick in. Hope it will help you if you struggle with something (and hope the naming will not change much in future). It’s also nice to see the whole infrastructure fits together and changing provider, in my case for Firebird’s, doesn’t make the code to blow out.
The ObjectStateManager contains a lot of information about entities currently in context. In fact it contains complete entities too. So you can try to look into it before issuing query and use it as local cache. For some simple cases, like PK match, you can create extension method in no time.
But in current feature pack for Entity Framework 4 if you’re using new DbSet object you can find Find method, which does exactly this.
You provide PK value (or values if it’s composite) and it’ll first look for that object locally and if not found it’ll try to fetch it from database.
using (testEntities ent = new testEntities())
{
var data = ent.Masters.Select(x => x.ID).Take(1).First();
var item1 = ent.Masters.FirstOrDefault(x => x.ID == data);
// Find method will find it locally, no querying will be done
var item2 = ent.Masters.Find(data);
}
using (testEntities ent = new testEntities())
{
var data = ent.Masters.Select(x => x.ID).Take(1).First();
//var item1 = ent.Masters.FirstOrDefault(x => x.ID == data);
// here the Find method will not find it and will query database
var item2 = ent.Masters.Find(data);
}
It’s nothing from what you’ll be excited couple of hours, but every little counts.
The Entity Framework 4 CTP4 is out and contains nice improvements. One is especially handy. Well you was able to do the same even in v1 but it was a lot of work. The method is ModelMatchesDatabase in System.Data.Entity.Infrastructure.Database. You can get Database object easily through DbContext.Database. The method return boolean value and checks whether your database matches mapping or not.
Only not useful thing is the EdmMetadata table the whole infrastructure is using. I know it’s easier with it, but could be done without as well. I hope will be removed (or be optional) in future CTPs or final releases. Because then you’ll be able i.e. check that your mapping is valid for selected database, check that your database has expected structure from application’s point of view (i.e. after new release) or simply spin up you own custom database altering process and check results.
Scott Guthrie recently posted article about New Embedded Database Support with ASP.NET. This made me think about other options, Firebird in particular, and advantages and disadvantages. What I’m going to do is to very shortly introduce Firebird Embedded here and then compare it with features Scott wrote in his article.
Firebird Embedded, shortly, is Firebird database server in one DLL. No need to install etc., just load this DLL and use it. To be precise, there are some other DLLs, i.e. to support national charsets, but it’s still in under 10MB all. The database itself is built from same sources as “full” server and it’s not limited in any way.
Works with Existing Data APIs – as I said, Firebird Embedded is based on same codebase as “full” server, thus the SQL and API is same. And not only this, the ADO.NET provider for Firebird works with it and you’re programming using same thinking.
No Database Installation Required and Database Files are Stored on Disk – databases created by Firebird Embedded are stored wherever you want, with any extension. Firebird itself doesn’t have any master database, thus even the “full” server works with any (valid database) file.
Shared Web Hosting Scenarios Are Now Supported with SQL CE 4 – sure you don’t need to install anything with Firebird Embedded, that’s why it’s called Embedded. And not only this, from version 2.5, you can open database from different processes, i.e. IIS worker process and Apache workers or some console application doing something in background.
Visual Studio 2010 and Visual Web Developer 2010 Express Support – as the tool set for Firebird Embedded is exactly same as for “full” Firebird, you can use DDEX (aka Server Explorer support), Entity Framework (LINQ), …
Supports Both Development and Production – this is something I’m silently expecting. But yes, you can do the same with Firebird Embedded too.
Easy Migration to SQL Server – ahh, my favorite point. The Firebird Project has of course whopping number of tools to support migration from Embedded to “full”. The most used is … nothing. The databases are fully compatible and you can switch servers without any other tools, migration, conversion, … Just place it where you want it and connect to it, either with “full” or Embedded Firebird. And to switch your application? Again nothing. Same ADO.NET provider, just change connection string, if needed. Mostly you’ll add server IP address and maybe different path and manually switch server type, if you want. Really the migration is so simple. It is one minute task and thanks to same tool set, no matter what you version your targeting during development, your application will work with the other as well without any additional effort.
You like Firebird Embedded? I do, a lot. SQL Server Compact Edition 4 looks promising, but Firebird has something to offer as well. And recall, it’s based on same sources as “full” Firebird, very mature codebase, examined with tons of installations.
The ADO.NET provider for Firebird now fully supports all the new features in Entity Framework 4. Simple. If you’re eager to test it, grab the weekly build and enjoy.
We support the internal improvements as well as the visible changes like i.e. Model First or CreateDatabaseScript method. The template for creating SQL script is now part of sources and sure will be included in final package as well. I expect to improve it on your feedback and also the model generation from designer is pluggable so you can create custom one and use it there.
To support some new features in SQL generation I had to tweak it little bit and as with every change, there’s a change that something goes wrong. Thus I would be more than happy to get some feedback either that it works OK or any queries where it fails.
I’m so happy to cross this milestone about two months after final Visual Studio 2010, .NET Framework 4 (incl. Entity Framework 4) were released. You can expect the official release after some testing, it’s your turn .
Though Entity Framework 4 doesn’t support bulk action, you can do it. As Matthieu Mezil shows, it’s possible. Nice piece of code, Matthieu.
If you don’t wanna play with it and go straight, you can create a stored procedure for action you need to call it. That’s how I’m doing it. Maybe in next version of EF somebody will think about bulk actions and we’ll see it.
And the v3/v4 is pretty awesome. It’s really going through a lot of options and dealing with it. Worth at least looking at it and quickly thinking about it.
