Posts Tagged ‘Entity Framework’
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.
The EDM designer in Entity Framework 4 has a “nice” new feature. You, know in EFv1 you was forced to map all three CUD procedures or nothing. If you didn’t do that, the validation feature told you. If you do the same in EFv4 the validation succeeds. Great, you may think. Finally I can use just procedures I really want (if you whatsoever want to use SPs and tight the logic into database when using ORM) to use and the rest will be generated for me. But nope. In fact the error will be thrown in runtime(!!!).
It’s described in documentation. But I couldn’t help but wonder, who did this decision??? I hate every error in runtime, I like my static compile time checking. It’s much safer, in my opinion.
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.
I don’t know whether it’s somewhere specifically pointed, but the ObjectContext in Entity Framework v4 has two (three) new handy methods. And I like these.
It’s kind of escape hatch similar to DefiningQuery. First method is Translate<T>. It takes DbDataReader and materializes the data back into entities. It’s similar to Materialize method from EFExtensions. If you some code in pure ADO.NET and you don’t have time or resources to redo it in EF (or it’s way easier old way) you can rewire the result into existing objects. I like it. Whenever I’ll feel I need to get dirty (and probably due to performance reasons) I can do it pretty easily.
using (testovaciEntities ent = new testovaciEntities())
{
IDbConnection conn = (ent.Connection as EntityConnection).StoreConnection;
conn.Open();
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from master";
using (DbDataReader reader = (DbDataReader)cmd.ExecuteReader())
{
MASTER[] result = ent.Translate(reader).ToArray();
Console.WriteLine(result.Length);
}
}
}
The other method is similar and simplifies the process of getting dirty if you simply need to run you fine tuned query with neat and sexy constructs. It’s ExecuteStoreQuery<T>. This method simply allows you to run any sql command directly in store language (thus you can use all features your database offers) and fetch and materialize back resulting entities. Similar to this is ExecuteStoreCommand which is similar to ExecuteNonQuery from pure ADO.NET. But you can do this without the method easily too, the method is just more convenient.
BTW also note that the Translate method isn’t adding the entities into context, it’s just about fetching and materializing.
I updated my pretty big model today. Added absolutely simple table, no foreign keys, basic datatypes, simple PK. When I was calling CreateObjectSet<T> what a surprise - InvalidOperationException saying “Mapping and metadata information could not be found for EntityType”. That’s not so much descriptive.
Quick search thru internet resulted in suggestion that I’m missing some property in my POCO classes. I was not. I double checked it and I know that Entity Framework throws MetadataException listing all the missing properties (yep, I was there too, but it’s easy to fix). Another reason I found was that the metadata are not placed into resources or things are screwed up because of mixing two models in one project (to be clear, it is possible, but you have to carefully design your namespaces and names). Not the case either. I have only one model, metadata are there properly, other entity sets were working fine.
After loosing my mind slightly I found, rejected and then believed back again that it’s true, that you have misspelled property. I first rejected the idea because it was mixing the misspelled property and missing property together. And I knew about the MetadataException case. But the misspelled property actually results in InvalidOperationException exception with useless message.
Hopefully you’ll believe this (or any other solution found) faster than I did.
Yesterday there was a question in one list I’m following. Simply to share some recommendation for tool being able to reverse engineer the database structure and show some E-R diagram.
After I get time to reply with some suggestions, idea came to my head. The EDM designer is actually kind of reverse engineering tool. Did you realized that?
As the EF support for major database platforms is available it’s easy to get a basic overview of the database structure without leaving Visual Studio. Sure it doesn’t have some cool features of specialized programs, but on the other hand it’s already in VS (Express edition is free) and you don’t have to buy and install another product (if you’re not doing this often, but then you’re probably using the specialized program).
Entity Framework has a nice ability to wire up associations on both ends when the related entities come into context. This feature is handy and you can use it to simulate some scenarios, like this. But we’re on EFv4 time, everybody is using POCO.
In last project I used POCO too, just to make myself more familiar with the way how it works in EFv4 and to face some challenges. Last week I was designing some flow, and I needed to have some related objects in memory too. I immediately jumped into Include method (actually I used this improved version). But later I couldn’t help but wonder would the automagic association wiring with (pure) POCOs?
I created simple master-detail scenario and created these POCO classes (note I’m not going to use proxies).
class Master
{
public int ID { get; set; }
public string Foo { get; set; }
public ICollection<Detail> Details { get; set; }
public Master()
{
this.Details = new List<Detail>();
}
}
class Detail
{
public int ID { get; set; }
public string Bar { get; set; }
public Master Master { get; set; }
}
And created test code (testEntities is a simple ObjectContext).
using (testEntities ent = new testEntities())
{
ent.ContextOptions.LazyLoadingEnabled = false;
var details = ent.Details.ToArray();
var q = ent.Masters;
foreach (var item in q)
{
Console.WriteLine(item.Details.Count);
}
foreach(var item in details)
{
Console.WriteLine(item.Master.ID);
}
}
If you run the code, you’ll see it works as expected. Exactly like the generated classes, although these are (pure) POCO classes – no need to use proxies or any special collections. I like the smart work done behind by Entity Framework for me.
I’m using a lot of features from Entity Framework. In fact I think sometimes I’m abusing some features or pushing these to limits. Due to this, I’m often using designer as well as directly editing the XML (maybe if I could do “Update Model from Database” I would use XML almost all the time). But it’s a pain to be forced to close the designer when opening the file in XML Editor and vice versa.
Luckily there’s a solution. Open another instance of Visual Studio, there open the file in one “view” and the other “view” open in your solution as normal. When you change your file in one instance, the Visual Studio on the other will detect it and you can reload the file.
This will save me (and hopefully you) couple of unneeded clicks.
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.
Some time ago I was writing about not supported scenario when you have entity with complex type and you want to map result of stored procedure to this entity. I couldn’t believe that this, from my point of view fairly common, scenario will not be supported in designer in RTM.
Right now I’m running RC of Visual Studio 2010 (the development probably in feature freeze mode) and the limitation is still there. 
Jeff Derstadt was absolutely right, unfortunately. Although Zeeshan Hirani in forum said, he was able to do it manually, it’s uncomfortable. Same as complex types in EFv1.
Looks like complex types, sadly, are still not first class citizens for designer.
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 uncovered a magic command from Entity Framework v4 when you create M:N association and the underlying table is defined with both columns as identity and you insert there. I don’t what’s it good for, as this table in fact only stores the two IDs to connect other tables. But somebody may build some logic on identity there, sure.
When I first saw the command, I was completely stunned. I had no idea what’s going on there and whether I see there one or more commands. You can have fun too:
declare @generated_keys table([ID_A] int, [ID_B] int)
insert [dbo].[A_B]
output inserted.[ID_A], inserted.[ID_B] into @generated_keys
default values
select t.[ID_A], t.[ID_B]
from @generated_keys as g join [dbo].[A_B] as t on g.[ID_A] = t.[ID_A] and g.[ID_B] = t.[ID_B]
where @@ROWCOUNT > 0
As an old school guy I was first looking for semicolons and then later tried to decode it by “parsing” the content.
Isn’t it nice…
EdmGen2 is a nice tool. Especially if you know EdmGen you may find it useful. I.e. you may speed up the start of you application by pregenerating views directly from EDMX file.
Unfortunately if you try to use it with EFv4 it will crash. But we have sources, why not to fix the problem? And that’s what I did.
First problem was with new namespaces the EFv4 EDMX file has. The new ones are:
static string csdlNamespace = "http://schemas.microsoft.com/ado/2008/09/edm";
static string ssdlNamespace = "http://schemas.microsoft.com/ado/2009/02/edm/ssdl";
static string mslNamespace = "http://schemas.microsoft.com/ado/2008/09/mapping/cs";
The next step is to switch the project to target .NET Framework 4, you can do it in project options. And finally check whether the references, especially System.Data.Entity.Design, where the interesting objects are, are pointing to “4.0.0.0 versions” and correct if needed.
Done. Build and use. And if you want to have it without work, grab this file with all changes already done .
I like the idea of complex type. Sadly the designer didn’t supported complex types in EFv1/VS2008, although you could use it manually editing the model (it’s just a XML file). The designer in EFv2/VS2010 supports complex types (and you can use it also with EFv1, see this) hence it’s time to start really using these.
Unfortunately nothing is perfect. As far as you don’t wanna map some stored procedure result to collection of entities, then the designer will tell you, that it’s not supported. And Jeff Derstadt pointed in forum that this limitation will not be solved in RTM.
I’m using EFv4 in my current project and thanks to ability to map functions directly to LINQ functions via EdmFunction attribute I was able to to a lot of work via functions and queries created by EF itself. But I always feel a little bug in my head saying “What if you’ll need it later?”. Sure I can redo all back into direct properties, but if it’s close to the end of the project it’s pretty expensive to do it.
What a pity, I see complex types as a good concept, but I’m too careful to close myself the path with SPs.
