Posts Tagged ‘Entity SQL’
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.
As it may look like nothing is going on, it’s not true. Next to Firebird 2.5 new protocol features, I’m also working on Entity Framework v4 support. Before I go further, be sure, that all providers written for Entity Framework v1 are also working with v4.
In fact right now all the major improvements in Entity Framework v4 are supported. You can benefit from features available, like the LIKE translation support or plenty of new functions. My personal favorite is TruncateTime (so I can get rid of workaround). The Model First approach is next in a row. At least basic T4 template for start is my aim. The rest could be done by you, simply modifying the template. And also wiring the template into code so you can use it programmatically too. Under the cover, while working on new stuff I’m also finding ways to optimize the code. Luckily the changes will be noticeable. 
The DDEX for Firebird supports Visual Studio 2010 and the full Entity Framework v4 support will be here soon – now you can try a weekly build. Feel free to ask about anything related.
From time to time you may need to compare only date part of datetime/timestamp field in your database i.e. for filtering. In EFv1 this is a little bit problem, in EFv4 (now beta 2) better.
Let’s start with EFv1. If you try to write something like this:
.Where(x => x.DateTimeColumn.Date == DateTime.Today)
You’ll end up with nice exception. Simply EF is not able to translate it. To solve this problem I created handy (for me) method, that will do simply expansion comparing Day, Month, Year, so you don’t have to write it over and over again.
public static Expression<Func<TElement, bool>> DateEqual<TElement>(Expression<Func<TElement, DateTime>> valueSelector, DateTime dt)
{
if (valueSelector == null)
throw new ArgumentException("valueSelector");
ParameterExpression p = valueSelector.Parameters.Single();
Expression ex = Expression.And(
Expression.Equal(
Expression.MakeMemberAccess(valueSelector.Body, typeof(DateTime).GetMember("Day").Single()),
Expression.Constant(dt.Day)
),
Expression.And(
Expression.Equal(
Expression.MakeMemberAccess(valueSelector.Body, typeof(DateTime).GetMember("Month").Single()),
Expression.Constant(dt.Month)
),
Expression.Equal(
Expression.MakeMemberAccess(valueSelector.Body, typeof(DateTime).GetMember("Year").Single()),
Expression.Constant(dt.Year)
)
)
);
return Expression.Lambda<Func<TElement, bool>>(ex, p);
}
So you can write i.e.:
.Where(Ext.DateEqual<DateTimeEntity>(x => x.DateTimeColumn, DateTime.Today.AddDays(-20)))
One modification I have in my head, is to extend it to support comparing two columns in database. But that shouldn’t be hard.
On the other hand, EFv4 contains couple of new canonical functions, for datetime/timestamp as well. One that’s useful for this case is TruncateTime, also exported for LINQ usage with EdmFunctionAttribute. With it, you can write queries little bit easier. Still not directly .Date, but i.e.:
.Where(x => EntityFunctions.TruncateTime(x.DateTimeColumn) == DateTime.Today)
Comparing two columns is available too. Sure, your provider needs to support this new function, but that should be no problem for all provider writers.
Maybe the future EF improvement could be to support .Date for translation too.
Nová verze Entity Frameworku už pomalu klepe na dveře a proto není od věci podívat se, co nového nabídne. V rámci přednášky na MS Festu projdu největší změny a vylepšení. Rozhodně je na co se těšit – pokud již EF používáte, tak na ulehčení života a pokud ještě ne, tak na ještě větší lákadla jej použít. A vzhledem k plánované večerní akci bude prostor i pro kuloární diskuze, nejen o EF.
Pokud jste nestihli první pražskou nebo brněnskou přednášku, máte v Praze další šanci. 3.11.2009 (úterý) od 17:20 v rámci programátorských večerů na MFF UK.
Více info zde nebo zde, registrace na http://akce.altairis.cz.
Prezentace ke stažení.
Model Defined functions are new feature in EFv4. You simply define you function using EDM functions etc. in your model and then you can use it in your queries. With EdmFunction attribute you can also create stub function to use it in LINQ queries. That’s all great, and even itself makes life with Entity Framework easier.
But if you call it from LINQ (my favourite way of querying), it’s kind of odd. You’re writing it as:
context.Persons2.Where(p => GetAge2(p) < 100);
And while I was preparing some demos for my presentation, there was a flash of idea in my head. “What if I define the function stub as extension method?”, I thought. Yes like:
[EdmFunction("testovaciModel", "GetAge2")]
static int GetAge2(this Persons2 p)
{
throw new NotSupportedException();
}
This should work, right? It’s just sugar and the translation should work without complaining. And it really does. You can now write:
context.Persons2.Where(p => p.GetAge2() < 100)
Sweet! You can still keep these MDF method stubs in one place but use it in more natural syntax.
And by the way, it works for store functions as well (you’re just limited on types).
[EdmFunction("testovaciModel.Store", "GetAge")]
static int GetAge(this DateTime born)
{
throw new NotSupportedException();
}
context.Persons2.Where(p => p.Born.GetAge() < 100)
I’m especially happy for store function exposed to LINQ. I’m using these in a reasonable amount in my databases and being able to filter using the function without wrapping the query into i.e. stored procedure or view is neat.
20.10.2009 (úterý) od 17:00 budu přednášet o novince (je to ještě novinka, když .NET 4 je za dveřmi?) v .NET 3.5 SP1 – Entity Framework. Tentokrát v Brně. Stejně jako v Praze se podíváme na nové vlastností, které přinese Entity Framework v4 v .NET 4, stejně tak, pokud zbyde čas, přijde na řadu rychlé info o ADO.NET Data Services (Astoria).
Registrace na http://wug.cz/Aktuality/tabid/36/ctl/Detail/mid/492/ItemId/303/language/cs-CZ/Default.aspx.
Prezentace ke stažení.
13.10.2009 (úterý) od 17:30 budu přednášet o novince (je to ještě novinka, když .NET 4 je za dveřmi?) v .NET 3.5 SP1 – Entity Framework. A nejen to. Zabředneme také do nových vlastností, které přinese Entity Framework v4 v .NET 4. Pokud zbyde čas, na řadu přijde rychlé info o ADO.NET Data Services (Astoria).
Více info, včetně registrace na http://akce.altairis.cz/Events/298.aspx.
Prezentace ke stažení.
There’re two kinds of people. 1) people doing almost everything in code; 2) people doing everything on database side. I’m in neither of these buckets. I like doing a lot of stuff on database side, because sometimes expressing something in set operations (these are good for RDBMS) is really challenging. On the other way, when it’s easy and fast to do it in code, why bother…
From this few sentences you can guess that I like writing stored procedures. Because of this in project I’m currently working on I created some stored procedures for difficult and expensive searching and I’m mapping results back to entities in Entity Framework. The problem is that this particular entity has a lot of associations. Thus it’s more than likely somebody will need the related entities too. Sadly there’s no Include (stored procedures are not composable by default, so you cannot create left join to fetch the related data). But that was a problem, because loading – using Load method – x related entries for even small with i.e. 20 items results in 20×x calls to database. Although these queries are in most cases cheap, it’s not good for performance.
So I started creating extension methods to get this solved in a little bit better way. My goal was to have one query for one related end for all items in result. Hence there will be only x additional queries. The result is here:
Disclaimer: The code is not general purpose and contains some assumptions based on my conditions and rules.
public static IEnumerable<T> LoadRelated<T>(this ObjectResult<T> result, MergeOption mergeOption, params Func<T, IRelatedEnd>[] relatedEnds)
where T : EntityObject
{
return LoadRelatedStarter(result, mergeOption, relatedEnds);
}
public static IEnumerable<T> LoadRelated<T>(this ObjectResult<T> result, params Func<T, IRelatedEnd>[] relatedEnds)
where T : EntityObject
{
return LoadRelatedStarter(result, MergeOption.AppendOnly, relatedEnds);
}
private static IEnumerable<TEntity> LoadRelatedStarter<TEntity>(ObjectResult<TEntity> result, MergeOption mergeOption, params Func<TEntity, IRelatedEnd>[] relatedEnds)
where TEntity : EntityObject
{
result.EnsureNotNull();
TEntity[] tmp = result.ToArray();
relatedEnds.EnsureNotNull();
relatedEnds.EnsureEachNotNull();
if (tmp.Any())
{
for (int i = 0; i < relatedEnds.Length; i++)
{
ObjectQuery query = relatedEnds[i](tmp[0]).CreateSourceQuery() as ObjectQuery;
Type related = query.GetType().GetGenericArguments()[0];
typeof(Extensions)
.GetMethod("LoadRelatedHelper", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Static)
.MakeGenericMethod(typeof(TEntity), related)
.Invoke(null, new object[] { tmp, query, mergeOption });
if (i == relatedEnds.Length - 1)
{
FixAssociations(query.Context, tmp);
}
}
}
foreach (TEntity item in tmp)
{
yield return item;
}
}
private static void FixAssociations<TEntity>(ObjectContext context, IEnumerable<TEntity> entities)
{
// this is a workaround to make associations wire up properly
context.Refresh(RefreshMode.ClientWins, entities);
}
private static void LoadRelatedHelper<TEntity, TRelated>(ICollection<TEntity> entities, ObjectQuery<TRelated> query, MergeOption mergeOption)
where TEntity : EntityObject
where TRelated : EntityObject
{
string separator = string.Format("{0}) union ({0}", Environment.NewLine);
// In general this may produce wrong results
string queryTemplate = query.CommandText.Replace(query.Parameters.First().Name, "{0}");
StringBuilder newQuery = new StringBuilder();
newQuery.AppendLine("(");
for (int i = 0; i < entities.Count; i++)
{
if (i > 0)
newQuery.Append(separator);
newQuery.Append(string.Format(queryTemplate, string.Format("p{0}", i)));
}
newQuery.AppendLine();
newQuery.Append(")");
// I know (from my design rules) that there's only one (key) param
ObjectParameter[] parameters = entities.Select((x, i) => new ObjectParameter(string.Format("p{0}", i), x.EntityKey.EntityKeyValues[0].Value)).ToArray();
query.Context.CreateQuery<TRelated>(newQuery.ToString(), parameters).Execute(mergeOption).ToArray();
}
The idea is pretty simple. For each related end, grab the query and instead of using one parameter, add there all the value for all items in result – the primary key columns. I’m actually parsing the Entity SQL query returned to me, although with MetadataWorkspace one should be able to create it yourself (you can do it as a homework ). Then I modify the query, fill the parameters and execute it. Again, the parsing isn’t perfect, as well as the work with keys for parameters – there’s a simplification based on my conditions and rules.
With the automatic association wiring (used also in this trick) this should work nicely. It works like a charm for ObjectQuery, but not for ObjectResult (in EFv1). I don’t know, maybe it’s a problem on my side – anyway it’s reported in EF forum, so far without reply. If you read the second post there from me, you’ll find the workaround I found. This idea is captured in FixAssociations method (and may result in a huge or clause for big results).
These methods are taking array/params of related ends to get all in one method. And finally you get the result back and you can start processing it. The result is, for me, acceptable, I ended up with x+1 queries (with i.e. EFExtensions, I think, one can be able to do this with even lower number of queries).
Anyway, hope this helps and hope I find why the associations are not wired up properly. Maybe some considerations to improve this scenario are worth to discuss for post-EF4, as as far as I know, there’s no improvement on this in EF4.
Note: EnsureXxx are my runtime validation extension methods, similar to Code Contracts.
