eSqlBlast – Entity SQL Query Helper
I could not have figured out some of these more complex Entity SQL queries without the help of a tool called eSqlBlast written by Zlatko Michailov (in his free time) who is the Program Manager for Entity SQL. The tool is available on Code Gallery along with a number of other tools for Entity Framework at http://code.msdn.com/adonetefx. With eSqlBlast, you can test queries and receive immediate feedback, whether it is an error message describing a problem with the query, or the results along with the generated SQL for the database. I have used the tool in its early stages and look forward to seeing it evolve.

I have been learning a lot about EDM QueryViews lately, though more of what I'm learning (the hard way) is what you can't do with QueryViews.

Most of what you'll find documented about QueryViews is to replace mappings so that your resulting entity is read only. But there are more uses for QueryViews. THe problem is that I"m having a very hard time discovering what those are and therefore it's trial and error (and some help from Srikanth on the EF team who seemed to get stuck with Holiday Weekend forum duty).

It started with an old hope of mine to be able to embed a FullName property directly into the model. I thought I could do this with QueryViews by adding a new property to the Entity called FullName and then using EntitySQL string concatenation syntax to combine Trim(LastName) + ", " + FirstName or at the least Concat(LastName, ",", FirstName).

After working out the various validation errors and then weeding out what causes them (Trim, Contact and +) I have come to the conclusion that you just can't use any functions at all. I could be wrong, but so far that's where I'm at.

In fact it took me a while to realize that this error

The query view specified for 'Contacts' EntitySet's type(s) 'IsTypeOf(BAModel.Contact)' contains an unsupported expression of kind 'Function'.

might not be telling me that the Trim function wasn't supported but that no Functions are supported I still can't tell, but expect to get some confirmation one way or another (and it *is* a holiday weekend in the U.S. so I'll be patient).

I'm still waiting for confirmation of my conclusion, but after going around in circles for days, I wonder if I've finally hit the nail on the head.

I also sent an email to the doc team to please include more details about writing QueryViews.

QueryView does support a bunch (but not all ) of the EntitySQL operators. You can do things like write CASE statements, perform UNIONS, test for ISNULL. So you can write some logic into them.

It also supports operators that can't be used in Conditional Mapping. WIth Conditional Mapping you can only test for = or IS NULL or IS NOT NULL. By using a QueryView you can add int greater than and less than, BUT  - on what?

I thought - ah okay, I'll try a condition on a date field.

SELECT VALUE BAModel.Contact(c.ContactID,c.FirstName,c.LastName,c.Title,c.AddDate,c.ModifiedDate)
FROM dbo.Contact as c
WHERE c.AddDate>="1/1/2007"

I was trying to emulate T-SQL here but I need a date, not a string. I thought that without a functino I was hosed again, until I discovered ENtitySQL's Literals and rewrote the query successfully this way.

So this gives me two benefits over Conditional Mapping. Not just the greater than operator, but testing against literals. Conditional mappings with "=" can only be used with strings and integers as strings (which therefore also covers booleans). However I haven't pushed the envelope on those, so maybe there's a way after all to use a Date in a literal as well.

At the same time, I'm still working out when I would use a Defining Query over a QueryView. One obvious scenario would be when I can't even express the query with a QueryView.

With a DefiningQuery, you can write native queries. Above, I'm close to doing that anyway, since this EntitySQL is written against the store layer not against the CSDL layer. So I can just back down one step further and write T-SQL queries inside the model which will return (read only, but updatable via function mappings) entities for me.

Not to say that QueryView is as limited as I'm discovering. We just need more code samples of when to use them besides the scenario of creating a read-only entity.

Well, not snakeS (pl.), just snake. I'm convinced that every time I see a snake around here, it's just the same one.

So THE snake reappeared a few days ago in an interesting place.

We have something called a Doggie Dooley buried in our yard.

I went to make a "deposit" in it a few days ago, opened the lid, and much to my surprise, there was a snake curled up on top of the contents. And this Dooley was nearly full.

Now I'll leave it to you to find out what a Doggie Dooley would be filled with and then you might understand why I found this a little surprising. I guess I have a lot to learn about snakes.

I did amuse myself for the rest of the day with a little song I made up dedicated to this snake that any five year old would have been proud of. But I'll keep that to myself.

I took a much needed break (mental and physical) from sitting in front of my computer fighting with a chapter that has been a bit of a challenge for me, and cajoled my husband into taking me for a walk. It's finally a beautiful day, about 70 degrees (F), sunny, light breeze. We have trails quite nearby our house that go up to the Long Trail, so went over to the one on the next road and hiked up to the shelter that is a stopping point along the trail, had a snack and hiked back down. It's definitely the most bang for the buck we can get when we can't spend a lot of time playing.

I brought my camera hoping that there would still be some spring flowers and boy were there a lot. I think because it's been a cold spring, things are a bit delayed.

My neighbor said she saw a big patch of LadySlippers along this path. Didn't see too many but looks like someone picked one from here.  

My hubby at his finest.

It's always funny to google for a solution to a problem and find your own blog post pop up with the answer.

I had to use an Entity SQL JOIN query today and I just went back to Chapter 5 to get a reminder of how to construct them. It's not the first time I've taken advantage of what is currently my own personal resource.

Boy it's feeling like it. So many nuances when you start looking at inheritance, complex types, query view, definiing queries , many-to-many mappings and then there are stored procedures. I'm wondering if I should just skip all of that in my book and plan to write a book on mapping and another one on stored procedures?

Okay okay, going back under my rock to keep at it...

This isn't even new news, but it is somewhat new to me! I met Damien at an SDR back in October and had fun sitting with him before I headed to the airport as he advised me that I should probably just get off the fence and move to C# finally. The discussion was mostly around the fact that I won't code on the fly in C# when I do presentations because it's not my natural coding language and I know I"ll do something wrong like use parens in an array and not be able to see why the thing won't compile. He suggested if I was always coding in C# instead of mostly in VB and sometimes in C#, this wouldn't be a problem.

I think it was the first time I heard someone make this argument in an attempt to truly be helpful and serious, not desecrating VB or laughing as they talked; therefore it left me with a great impression of him.

So I was very happy to hear that he took at  job with Microsoft recently and is working on the LINQ to SQL team. He's a really bright young guy. They actually snagged a few of these guys from the SDR. Jonathan Carter was there and became a new hire shortly after.

But to me what is most significant about this is knowing that the LINQ to SQL team is hiring developers. That says to me that they are continuing to work on LINQ to SQL. I get asked about this a lot when I present. I see  questions in listservs and on blogs - if LINQ to SQL is done and Microsoft is going to make everyone who has fallen in love with LINQ to SQL use Entity Framework instead - even if they don't want to. Even Roger Jennings  questioned this in a deeply analytical blog post. (I'm starting to wonder if Roger moonlights as a trial lawyer. )

I have even seen suggestions that LINQ to SQL was intentionally cobbled and done so for political, not technical reasons. I dunno, maybe I'm just too naive; but I find that hard to believe. OTOH, I have worked for myself for nearly 20 years, so I am a little out of touch (and very happily, at that!) when it comes to corporate politics.

But I really don't believe that this is going to happen. I do believe that LINQ to SQL will evolve and, like datasets, remain a completely viable choice for data access in .NET. I can't say why it didn't get a lot of attention for SP1, but comparing it to the fact that Entity Framework has been developed out of band and therefore introduced a lot of new stuff between Beta3 and the RTM version that will be part of SP1 is comparing apples to oranges.

So knowing that they are getting fresh blood (and I don't mean warm bodies) in the form of developers on the LINQ to SQL team is, to me, an indication that the product will continue to evolve.

And of course, thanks to my memory of that conversation last fall, I have already suggested to Danny Simmons that he task Damien with writing some VB samples! Hee hee.

I was getting ready to suggest this feature because it's a pain to "move" properties if you are deriving types or doing entity splitting.

I actually use the method of going into the EDMX file and copying the properties around in there so that I don't have to worry about the attributes and facets. Especially when I have a lot of properties to move.

Just out of curiosity I right clicked on an scalar property in the designer and saw Copy on the context menu, so I clicked it. Then tried pasting it into another entity and VOILA! Not just copy and paste; but cut, copy and paste

The funny part is that may have existed in the previous bits but I never noticed it before.

I can go shorten my book by a whole page now!

(I also want you to know that I was a good girl and removed "Holy Crap!" from the blog title and replace it with exclamation that is there now.)

The SP1 Beta does not include updated documentation for Visual Studio 2008, but it is all online.

You can start here: http://vs2008sp1docs.msdn.microsoft.com/en-us/default.aspx

A direct link to EF documentation is: Entity Framework

The tools documentation is not contained within there. Instead it is here: Entity Data Model Tools

The Astoria documentation is here: ADO.NET Data Services Framework

To me the most helpful thing is to see where it lives in the hierarchy so here's a pretty picture

Check out Ryan Hauert's blog!

Only two EF posts so far, but based on his busy postings in the forums, I imagine there will be more...

While I was in the middle of a flurry of conference prep and travel in April, I got an email pointing me to an interesting blog post by Dave DeWinter who is using T4 Templates to generate entity friendly CUD stored procedures. Why "entity friendly"? Because of a number of rules enforced by the model such as:

If you are using Stored Procs to do function mapping to an entity, you must use all three operations: Insert, Update and Delete.

if you have an association, the foreign key (aka the ID through the navigation property, e.g. Customer.Address.AddressID) must be used in all three (Insert/Update and Delete) stored procedures

If you do function mapping for a single entity in an inheritance hierarchy, you must have mappings for all of the entities except for abstract base types in the hierarchy.

I have been fighting the concept of modifying the database to enable the model (yeah - call me data-centric yet again and call em "she who worries about annoying the DBA" even though all of the DBAs I've ever met are pretty easy going)) but something has to give somewhere.

Dave and Ryan Hauert (who has been very active in answering forum questions for EF as of late!) are very pleased with the results of their templates.

Interestingly, at DevConnections, Danny Simmons was talking about providing functionality like this in a future version of Entity Framework.

So I'm going to go check this out, and play with it a little bit and see how it goes.

Can somebody tell me why CNN thinks this is a good idea? (Besides the profit motive...)

I've been spending a lot of time futzing with stored procedures in Entity Framework and losing sight of the fact that one of EF's core features is its ability to do query processing - and that is something it does very very well.

The Stored Proc support is obviously critical for many and while it's there, some scenarios are pretty easy to pull off, while many others are possible  but not so easy to implement and that can get frustrating.

What I'm trying to do is just truly see which things are easily pulled off in the designer (and discovering more than I realized), which are doable and which might take a hack or two to get around.

I think that in my case I was focused on the trees and forgot the the forest.

Thanks to the person who helped remind me of that.

Scott's a pal, promoting my book and my workshop on twitter!

Matthieu Mezil is a Parisian who really digs .NET and the Entity Framework. He writes a lot about it in his native tongue (that would be French ) in his blog.

Since my French, which I studied for 4 years in high school and college (which was a long, long time ago) is pretty rusty, I use google's translation to get a gist of what he is saying in his blog.

But now that Matthieu is an MVP, he has started a mirror blog in English on the msmvps.com/blogs site.

So to recap

Matthieu Mezil in French

Matthieu Mezil in English

I hadn't noticed this before.

If you have a stored procedure that is mapped to an entity such as "GetCustomersSince" which returns Customer entities, the command is executed when you create the query, not, like with normal queries, when the data is first needed.

In case that sentence was too convoluted...

Here is a LINQ to Entities query (the same is true for LINQ to anything and also for Object Services (context.CreateQuery))

Dim query= From c In context.Customers Select c

For Each cust in Query  <-- command is executed here

'do something

End For

But when calling a function import:

dim custs= From c in context.GetCustomersSince(New Date("2008","01","01")) <--command is executed here

For Each cust in custs

'do something

End For

Looking at the code gen for the function I can see that ObjectContext.ExecuteFunction is called.

I'm definitely interested in what people have to say about Danny's post so I have subscribed to the Bloglines citations of it:

http://www.bloglines.com/search?q=bcite:blogs.msdn.com/dsimmons/archive/2008/05/17/why-use-the-entity-framework.aspx

RSS

Don't forget the comment thread in Danny's post as well.

Not surprisingly, the folks who have already made it clear that EF is way too data-centric for them are up in arms and there are some very interesting discussions in their blogs (as well as the comment threads).

I'm not sure if I would compare the investment I am making in understanding how EF works and writing a whole book to share what I've learned to being brainwashed by a "shiny brochure on your desk". Nor do I feel like a "cheerleader" or "parrot". But... whatever... (lame response? Yep! But it's all I have time (or patience) for).

Michale de la Maza follows up his popular C# day events with a VB.NET day at the Microsoft office on Tuesday May 27th.

Join us for a relaxed, no cost introduction to VB.NET programming. By engaging in participatory activities, you will learn some of the most common and powerful features of the VB.NET language. This day is not lecture-based. There will be fewer than 60 minutes of lecture material -- almost all of the time will be spent on participant-guided activities. Everyone who attends will learn what interests them at their own pace in a relaxed, tension-free environment with an experienced VB.NET developer and trainer available to answer questions.

RSVPs are required and details are here.

The online registration system for Microsoft events is currently broken (or just acting really flakey).

If you are trying to register for Thursday’s free, full-day, Bob & Chris & Jim Roadshow , try the online registration first here:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032378634&culture=en-US

If that doesn’t work, you can register by phone

Call the event system directly (877.673.8368) and use the event ID 1032378634.

For more information and details about the event, see Chris’ blog post here:

http://blogs.msdn.com/cbowen/archive/2008/05/02/the-roadshow-rides-again-may-june-edition.aspx

If you are trying to register for a different one of these Roadshow events, just get the EVentID from Chris' blog.

If you are trying to register for other Microsoft events the phone # should be the same, but you'll need to know the EventID of the event you are trying to register for.

In the earlier bits, if you changed an association to an inhertiance, the type that becamse the derived type lost its mappings and th emappings for any associations.

Depending on how complex that type is, recreating all of that could be challenging.

I just tried this out in the new bits and the mappings and association mappings all remain intact.

This is great and it will save a tree too, since I can go remove all of that explanation about how to deal with that problem from Chapter 5 of my book!