Posts Tagged ‘Entity SQL’
Yesterday I wrote a post about a possible confusion that might be introduced with new version being version 5.0. Diego Vega wrote a follow up post describing the rationale behind it (and it was also noted in comments in previous post). Simply the team is now sticking to semantic versioning no matter what. Hope that will last a while. Let me recap.
The first version was, hey, version 1. The second version was version 4, because the numbering being unified with .NET Framework. Then there was a bunch of CTP, vNext, Preview, Alpha/Beta versions something being just update with version 4 core, something with new core. Hard to track what was what. Decision to use semantic versioning was made and the numbering was cleared a little, simply to 4.x and whatever version comes next. Still on track? Because of the semantic versioning rules, the next version is going to be 5. I thought the previous versioning decision would take precedence (though I think the .NET Framework version could be 5.0 as well) and it will be 4.5 as well.
I was wrong. The semantic versioning is now the way Entity Framework is going no matter what. Good. Hope the third version will be still using this scheme and will be version 6.
So. Next version is going to be version 5(.0.0) and will be delivered with .NET Framework 4.5.
There’s a follow up post.
I’m a little bit perplexed. Entity Framework 4.3 Beta 1 was just released. And there’s a sentence in the document that’s very interesting:
As soon as the next preview of the .NET Framework 4.5 is available we will be shipping EF 5.0 Beta 1, which will include all these new features.
Does it mean that the version that will ship with .NET Framework 4.5 (the next version in time of writing this post) is going to be 5.0? I still clearly recall the mess the second version with version number 4.0 created – a lot of people, on my trainings, talks, …, confused. What happened to clear versioning strategy?
I hope it’s just a small miss/typo and the version will be Entity Framework 4.5 and the following (additive) updates will be 4.5+n.
I’m happy to bring you early Christmas gift packed as ADO.NET provider for Firebird version 2.7. This version brings important bug fixes (tracker.firebirdsql.org) and logging improvements.
This release wouldn’t be possible without support of people/companies using provider actively. Big thanks to them.
You can download it at www.firebirdsql.org or use NuGet package.
Enjoy!
Few days ago there was a question on Twitter in #efhelp about adding custom logic to every query in Entity Framework. It’s pretty easy and you can do it absolutely transparently so nobody using your code needs to know.
First you need to focus you Entity Set (not Entity Type) in Model Browser window.
Here set the access modifier to i.e. private or (internal/protected) and rename it to something else, so it’ll not interfere with original name of property we’re going to create. I often use X prefix (especially for properties on entities).
Now it’s pretty easy to create some logic. Here I simply added filtering to always only fetch entities younger than 10 days from now.
partial class Model1Container
{
public IQueryable<FooBarEntity> FooBarEntitySet
{
get
{
DateTime d = DateTime.UtcNow.AddDays(-10);
return XFooBarEntitySet.Where(fb => fb.Created > d);
}
}
}
And that’s it. Not a difficult task. But also note that through Entity SQL (or i.e. reflection) somebody might be still able to access original entity set and get access to the data. So it’s not rock hard security solution.
When I’m teaching my Entity Framework trainings, I’m always begging to look, at least from time to time or when you see the query looks complex, to generated SQL statement. And if you have (near to) real data, also execution plan. Although Entity Framework helps you with standard data access layer, it’s not magic – the query translation is complex process and sometimes what you capture in LINQ query isn’t exactly how you’d express it in SQL. You simply have different concepts in LINQ vs. in SQL.
Last week I was writing some decision algorithms based on data and I was accessing it, of course, using Entity Framework. Because the conditions we’re complex I was writing these as it came from my head to my fingers. The day after I was writing similar condition, only one or two options negated and I wrote it differently. Basically I was swapping All and Any methods. These two are interchangeable, if you change conditions accordingly.
As an example let’s have and condition: “All apples are green.” aka “All(apple => apple.Color == Green)“. But you can also say “No (any) apple is non-green.” aka “!Any(apple => apple.Color != Green)“.
Now the magic comes to play. You might think, well, if it’s interchangeable, then it’s good, as Entity Framework can always utilize EXISTS predicate from SQL. For simple queries maybe. But if you think about various places where the condition can occur and how easy is to negate the condition you immediately have a lot of problems in front of you. Add to this database engine optimized, where it can or can’t use properly indices, reorder conditions to create smaller intermediate result sets etc. A lot of places where the machine needs to (try to) figure out what’s best way of getting your data for you.
Sadly there’s no rule of thumb, like always use Any. Only one good and 100% working advice is to always check the query and execution plan. But even with i.e. All the result could be absolutely fine.
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.
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.
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.
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.
