Posts Tagged ‘LINQ’
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 I have to run two or more queries that I know will always be two or more – like some first/skip records and also total count. If you write it as two queries and execute, that means two round trips to database. Although it may not matter if the network latency is very small, why not to challenge myself and try to find some workarounds.
Sure you can create some stored procedures and get the data back from these, but I was thinking about more LINQ to Entitiesish way. I recalled a way I one time used inside one project. Although it was done in pure SQL, it, as it turned out, works, kind of, for LINQ to Entities as well.
The idea is using “one row table” and put the queries as columns. Let me demonstrate:
select
(select foo, bar from table1 where ...),
(select baz, foo from table2 where ...)
from OneRowTable;
Where the OneRowTable can be specially created table or i.e. for Firebird RDB$DATABASE or for Oracle Database dual. It isn’t the nicest SQL (and also challenges optimizer), but works. In columns as queries you can put anything you want as long as it is syntactically correct.
OK, what about the Entity Framework or LINQ to Entities respectively. I created the “one row table” first:
create table OneRowTable(x bit primary key);
insert into OneRowTable values (0);
The table needs to have the primary key to be able to import it into entity model, the datatype doesn’t matter (I was using MS SQL, hence the bit).
What about the queries? Similar approach:
var allinone = context.OneRowTable.Select(_ => new
{
AData = context.a.Where(a => a.x.HasValue && a.x.Value > 10).Select(a => new { A1 = a.id, A2 = a.id * 2 }),
BData = context.b.Where(b => b.id < 999).Select(b => new { B1 = b.id, B2 = b.y }),
});
string query = (allinone as ObjectQuery).ToTraceString();
var data = allinone.First();
var adata = data.AData;
var bdata = data.BData;
The a and b are my testing tables. You can check there’s only one query executed. Encapsulating this into some method is only piece of cake.
And how the query looks like? Well for my MS SQL test:
SELECT
[UnionAll1].[x] AS [C1],
[UnionAll1].[C2] AS [C2],
[UnionAll1].[C1] AS [C3],
[UnionAll1].[id] AS [C4],
[UnionAll1].[id1] AS [C5],
[UnionAll1].[C3] AS [C6],
[UnionAll1].[C4] AS [C7],
[UnionAll1].[C5] AS [C8],
[UnionAll1].[C6] AS [C9]
FROM (SELECT
[Project1].[C2] AS [C1],
[Extent1].[x] AS [x],
1 AS [C2],
[Project1].[id] AS [id],
[Project1].[id] AS [id1],
[Project1].[C1] AS [C3],
CAST(NULL AS int) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS varchar(1)) AS [C6]
FROM [dbo].[OneRowTable] AS [Extent1]
LEFT OUTER JOIN (SELECT
[Extent2].[id] AS [id],
[Extent2].[id] * 2 AS [C1],
1 AS [C2]
FROM [dbo].[a] AS [Extent2]
WHERE ([Extent2].[x] IS NOT NULL) AND ([Extent2].[x] > 10) ) AS [Project1] ON 1 = 1
UNION ALL
SELECT
2 AS [C1],
[Extent3].[x] AS [x],
[Extent4].[id] AS [id],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS int) AS [C4],
[Extent4].[id] AS [id1],
[Extent4].[id] AS [id2],
[Extent4].[y] AS [y]
FROM [dbo].[OneRowTable] AS [Extent3]
CROSS JOIN [dbo].[b] AS [Extent4]
WHERE [Extent4].[id] < 999) AS [UnionAll1]
ORDER BY [UnionAll1].[x] ASC, [UnionAll1].[C1] ASC
Not exactly the original shape. The translator took another way creating two one row results and using union all to get it into one query. Except this, the query is in general the same (the explicit joins are as result same as the subselects, though little bit more confusing in this case).
Again, this isn’t general purpose way of doing it and may result in worse performance than running queries separately and I would recommend using it only after careful testing and on controlled limited set of queries.
Julie Lerman created a post about spatial data usage in Entity Framework. Her conclusions are correct. But because from last three projects I did on Entity Framework two of them are working with spatial data (every customer wants a Google Maps or Bing Maps on website, it’s like cup holders in cars) I would like share my solution as well some ideas behind why I used this approach.
First and foremost Entity Framework doesn’t support spatial data now. So you have to create some workaround. The idea getting the data through blob is exactly what I created. In my tables where I need to save some coordinates (it’s mainly a point) I create simple column with this data type – I consider this clean initial work better, because maybe sometimes/somewhere/somebody will work with this structures so to have it clean. Then for the workaround I add XXX_bin column where I’ll store the binary representation of spatial data and use it for Entity Framework. Something like this:
[Location] geography Default geography::STGeomFromText('POINT EMPTY', 4326) NOT NULL,
[Location_bin] Varbinary(max) NOT NULL,
Because I’m working only through Entity Framework I need to update the Location automatically as there might be (and are) indices or other stuff working with it. To make it transparent I’m using insert or update trigger:
if (update(Location_bin))
begin
update %tablename% set
Location = geography::STGeomFromWKB(Location_bin, 4326)
where
ID in (select ID from inserted);
end
If you expect somebody updating the Location column directly too, you need to create another trigger and make sure you’ll not end up in infinite loop.
To support the – in my case – points for UI developers I create in every entity computed property turning Location_bin into my LatLong struct. This struct is using Microsoft.SqlServer.Types namespace.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Types;
namespace FooBar
{
public struct LatLong
{
public double Lat { get; set; }
public double Long { get; set; }
public static LatLong FromSqlBytes(byte[] bytes)
{
SqlGeography g = SqlGeography.STGeomFromWKB(new SqlBytes(bytes), 4326);
return new LatLong { Lat = g.Lat.Value, Long = g.Long.Value };
}
public byte[] ToSqlBytes()
{
return SqlGeography.Point(this.Lat, this.Long, 4326).STAsBinary().Buffer;
}
}
}
public LatLong Location
{
get
{
return LatLong.FromSqlBytes(this.Location_bin);
}
set
{
this.Location_bin = value.ToSqlBytes();
}
}
This creates almost seamless support for other developers when using spatial data in Entity Framework – inserting, updating, deleting all works without notice from others (if you’re not looking into internal implementation).
There are only two problems, both solvable.
First one is that developer cannot filter/sort/… using Location property. This isn’t probably a big problem. As (s)he doesn’t have spatial methods (like i.e. Distance) available, so the queries will not make sense. Which brings me to the other problem and that’s the querying. With EFv1 I created stored procedures for these queries and returned data through these. Same works for EFv4. If you have strict set of results you need, this is probably easiest solution. The other one, working in EFv4 is LINQ function imports via EdmFunction attribute. For computations I’m going to use I create function in T-SQL doing the work and I import it into my solution thru above noted attribute. The simple Distance method may look like:
create function Distance
(
@Location_bin1 varbinary(max),
@Location_bin2 varbinary(max)
)
returns int
as
begin
return geography::STGeomFromWKB(@Location_bin1, 4326).STDistance(geography::STGeomFromWKB(@Location_bin2, 4326));
end
And
[EdmFunction("model.Store", "Distance")]
internal static int Distance(this byte[] location1, byte[] location2)
{
throw new NotSupportedException();
}
Sure, when calling this function for a big resultsets it may be a performance bottleneck (I recommend doing there as much work as possible, even at the price of more functions doing similar stuff, to not create a deep function calls.), but with additional filters it works well.
protected IQueryable<Something> SomethingInDistance(LatLong point, int distance)
{
byte[] spatialData = point.ToSqlBytes();
return this.Somethings.Where(o => o.Location_bin.Distance(spatialData) < distance);
}
Another way could be to create a view (if you know the parameters in advance) and do explicit join (table/entityset <> view/entityset) in EF. Or do the filtering on client (if reasonably small). And I’m sure I’ll find other ways how to get the data you want back, simply choose what fits your needs best.
Although it may look like a lot of work, it isn’t. In fact you’re pretty fast (there’s a high level of reusability) if you know what to do (and after one implementation you will).
Today I seen a code with good chance of hard to find mysterious bugs. Let’s start with database table structure we’re going to use for demonstration.
create table NullTest(id int primary key, b bit);
insert into NullTest values (0, 1);
insert into NullTest values (1, 0);
insert into NullTest values (2, null);
If you now try to query this table via LINQ (i.e. LINQ to Entities) you may get surprising results.
foreach (var item in ent.NullTests.Where(x => x.b != true))
{
Console.WriteLine(string.Format("ID: {0} t B: {1}", item.id, item.b));
}
Console.WriteLine("===");
foreach (var item in ent.NullTests.AsEnumerable().Where(x => x.b != true))
{
Console.WriteLine(string.Format("ID: {0} t B: {1}", item.id, item.b));
}
If you run this code, you’ll get different results.
ID: 1 B: False
===
ID: 1 B: False
ID: 2 B:
What happened? Is the database engine doing something wrong? Or is there a bug in LINQ? Neither of those. In fact both results are correct. The second one (evaluated in .NET on client) is obvious why it’s as is. But what happened in processing of first one (evaluated on database side)? The devil is in NULL logic. Every operation with NULL results in NULL or false if it’s a boolean operation. And this exactly explains the inconsistent result. In .NET null != true is true but in databases it’s false (because of the NULL rules described above).
Thus if you’re writing LINQ query for database, although the impedance mismatch should be hidden from you when using LINQ, you need to take into account different NULL handling in database engines and in .NET (or any common programming language).
Remember the DBNull.Value? That was explicit solution for this “problem”.
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.
Next Firebird conference is coming. This year in München, Germany. I’ve never been in München though it’s pretty close to Czech Rep. thus I’m looking forward.
You can find info about conference at firebird-conference.com. I’ll be speaking there too, hence if you’re interested in Firebird and .NET you should definitely come. Every day I have one session. I’ll be covering new (2.5+) protocol implementation in .NET client for Firebird 2.1, Entity Framework support (also new in 2.5) and finally you’ll see how to create Windows phone (formely PocketPC, …) application and accessing Firebird database.
And the conference will be also great place to meet people you know from list etc. and talk face to face about your Firebird related problems, challenges and solutions. If you have any specific need to show (related to my three sessions) feel free to drop line in comments.
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í.
Firebird doesn’t have identity/autoincrement columns like i.e. MS SQL or MySQL. Firebird has concept of generators/sequences (as know i.e. in Oracle). This is more powerful concept, but comes also with drawbacks, because you can say for sure, whether the column values is generated or not. This is causing problems, if you set StoreGeneratedPattern in your model manually and then updated model from database – it’s lost. Because this can be big pain in the ass, FirebirdClient can now report the column as “Identity” if you give it little help.
If you put into comment of column #PK_GEN#, then FirebirdClient will report it as generated primary key, resulting in StoreGeneratedPattern to be set to “Identity”. Hence you don’t have to every time manually change the model and easily use automatic fetching of the value from database when saving changes. If you wanna test it, grab build from weekly builds.
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í.
Most of the time you’re using properties in objects that are also in database. But sometimes you may need to create property in object that’s not in database – it’s used only in this application or it’s there custom logic. Then, if you wanna use it in queries, you’re out. You can use only those properties declared in table, obviously.
But there’s a solution. First, the property has to be created from some other properties, so you’re able to do the same in database. And of course, functions used there needs to be translatable to store language too. This may limit you, but there’s nothing you can do about it, except evaluating query on client side.
Let’s have a table like this:
create table Foo (
ID int primary key,
IsAccepted bit,
IsPaid bit not null,
IsPacked bit not null,
);
And you wanna have property IsReadyToShip, in application. So you create:
public bool IsReadyToShip
{
get { return this.IsAccepted.HasValue && this.IsAccepted && this.IsPaid && this.IsPacked; }
}
But with this property you’re not able to query for all Foos ready to ship. Luckily the solution is pretty easy. First you’ll create expression for this:
public static Expression<Func<Foo, bool>> IsReadyToShipExpression = f => f.IsAccepted.HasValue && f.IsAccepted && f.IsPaid && f.IsPacked;
Then you’l prepare static compiled version of this expression, just for performance reasons, you can compile it in getter everytime too:
protected static Func<Foo, bool> IsReadyToShipFunc = IsReadyToShipExpression.Compile();
And finally the property:
public bool IsReadyToShip
{
get { return IsReadyToShipFunc(this); }
}
Right now we have the same result as before – working property. But because we also have the expressions of the property (and incidentally it’s translatable to store language ), so we can use it for querying. You can use it in an easy way (this is in this case ObjectContext used in Entity Framework):
public IQueryable<Foo> FoosReadyToShip
{
get { return this.Foos.Where(Foo.IsReadyToShipExpression); }
}
Not the shortest way to do it. But if you don’t wanna to write the condition again and again (and lower the maintainability) this is a way to do it.
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.
The Beta 1 of Entity Framework v4 (confused with versions?) is out for a while and you may be tempted to check the new features comming. And why not with Firebird.
Good news is, that there’s no breaking change right now I’m aware of. Hence you can easily jump into VS2010, install DDEX and test it. Of course, some new features are now not fully functioning as it’s beta and/or because it’s not nailed down for provider writers (i.e. DDL script generation). But as far as something will be ready, I’ll try my best to get it into Firebird’s provider.
On the other hand, even the new singularize/pluralize function is nice and works with all databases, you still get all names in uppercase, only the added suffixes are lowercase. And that looks even more weird. You can fix it yourself manually or let computer do the work, but still wondering why there isn’t any option to fix this (as uppercased name is default when not quoted according to SQL standard).
Anyway, if you found any problems with EF4, let me know.
A lot of people get used to understand the ObjectStateManager class. Yep, it’s the place where all the change tracking magic happens. But there’s also one other place that’s interesting. It’s MetadataWorkspace. With this class’ properties and methods you can find almost everything about stuff in your model, in runtime.
The information inside MetadataWorkspace is among others organizes into so-called spaces (DataSpace enum). Probably CSpace and SSpace are the most used. The CSpace contains stuff you have in your conceptual model (what you see in designer) and SSpace contains stuff from store model, so the database “structure”.
The items in MetadataWorkspace can be anything from the Metadata Type Hierarchy (definitely look at this link) starting with GlobalItem. This contains i.e. EntityType, ComplexType or EdmFunction. These types inside are containing a lot of other useful information. Thus when you want to show i.e. all tables and primary keys for these tables you can use (don’t forgot to first, for example, query for one record to load metadata or use this trick):
MetadataWorkspace mw = ent.MetadataWorkspace;
var items = mw.GetItems<EntityType>(DataSpace.SSpace);
foreach (var i in items)
{
Console.WriteLine("Table Name: {0}", i.Name);
Console.WriteLine("Keys:");
foreach (var key in i.KeyMembers)
{
Console.WriteLine("t {0} ({1})", key.Name, key.TypeUsage.EdmType.FullName);
}
}
I’m first asking for all entity types from store space, which gives us all tables there. Then I’m simply inspecting the properties to get some useful info – first the name of table and then all key members reading name and type (EdmType). Also the MetadataProperties is definitely worth looking (see below). It’s just a tip of an iceberg, and that was not difficult. You can literally get any information you’ve ever seen in your model.
And not only what you’ve seen in your model directly. For instance, the provider may expose some store specific functions you can use in ESQL queries. So the code:
var functions = mw.GetItems<EdmFunction>(DataSpace.SSpace);
foreach (EdmFunction function in functions.Where(f => (bool)f.MetadataProperties["BuiltInAttribute"].Value))
{
Console.WriteLine(function.FullName);
}
Gives you all of these functions. If you remove the condition you’ll get all functions, including stored procedures of user defined functions. Wanna parameters of these functions? No problem, it’s there. Just check Parameters property.
And that’s only the SSpace. The conceptual part of model is described there too. To see how much is the model interconnected (i.e. to consider pregenerating views) simply run:
var entities = mw.GetItems<EntityType>(DataSpace.CSpace);
foreach (var entity in entities)
{
Console.WriteLine("Table Name: {0}", entity.Name);
Console.WriteLine("Navigation Properties#: {0}", entity.NavigationProperties.Count);
}
Interesting may be also the object model aka objects created from conceptual model. Although I can continue with some more or less useful examples – to see all to posibilities (and also hit the accessibility limitations), try it yourself. And if you stuck, feel free to ask (here or in forums).
Jarek Kowalski posted to MSDN Code Gallery wrappers for any ADO.NET Entity Framework provider with ability to do caching and logging.
For me especially the logging (EFTracingProvider) is interesting, as I’m always checking queries sent to database. For selects it’s plausible with ToTraceString method (but not with i.e. query.First()), but looking for CUD is not so easy. With EF provider for Firebird, you can check, by default, Output window of Visual Studio, where all queries are placed or use any listener to write these i.e. to the file (this logging is only in debug builds of FirebirdClient).
I was thinking about creating some wrapped provider, but this is better (and without work ).
