Tuesday, May 6, 2008

LINQ Dynamic Queries

                No comments   
I came across an article this morning that helped me with a LINQ query issue that I was trying to work through regarding dynamic queries.
 
 
My situation was very similar; however, in my case I was querying an ENTITY rather than SQL directly; however, the same rules applied.
 
I knew I could easily write a query that was not type safe and not checked at compile time; however, the thought of doing that really bothered me because I wanted to leverage the full power of LINQ and keep everything type safe and checked at compile time.
 
I ended up taking advantage of Lambda Expressions:

Public Function Search(ByVal Name As String, ByVal City As String) As IEnumerable(Of MyTable)
     Dim MyEntities As New DbEntities()
     Dim MyQuery As ObjectQuery(Of MyTable) = MyEntities.MyTableSet
     Dim MyResults = From x In MyQuery Select x

     If Not IsBlank(Name) Then
          MyResults = MyResults.Where(Function(e) e.Name.Equals(Name))
     End If
 
     If Not IsBlank(City) Then
          MyResults = MyResults.Where(Function(e) e.City.Equals(City))
     End If

     Return MyResults.AsEnumerable
End Function
 
More information on Lambda Expressions can be found here:
 

ADO.NET Entity Framework Beta 3

                No comments   
I started working on a new application for a client this week that is going to use the ADO.NET Entity Framework. Since it is not final release yet I had to download and setup the ADO.NET Entity Framework Beta 3. It wasn't too bad to get installed but I did run into a few issues along the way that I thought I'd share to save you some time if you end up having to do something similar.
 
I ended up having to install the following in this order:
 
ADO.NET Entity Framework Beta 3
 
ADO.NET Entity Framework Tools Preview can only be installed if a necessary Visual Studio patch is installed.
 
ADO.Net Entity Framework Tools Dec 07 Community Technology Preview
 
At this point I had everything installed so I could add a ADO.NET Entity Data Model to my project.
 
Another good resource for ADO.NET related info can be found here:
 
 
One other thing I did notice when working with the ADO.NET Entity Data Model was that it all seemed to be very straight forward to setup and test if your UI and data access were in the same project; however, I found that when setting up a n-tier architecture I ran into some annoyances. The data layer I setup contained my ADO.NET Entity Data Model and it automatically creates an App.Config file with the necessary connection information and it creates the .csdl file, .ssdl file and .msl file in the data layer's /bin folder; however, when you add a reference to your data layer from your UI or business layer it does not automatically copy the connection string information into your Web.config file and it does not copy the .csdl file, .ssdl file and .msl file into the web application's /bin folder. So I ran into error messages until I finally figured out was going on.
 
To work arond this I created a Config/ConnectionStrings.config file and copied the connection information from the App.Config file into the new config file and had the Web.config reference the new config file. Then I copied the .csdl file, .ssdl file and .msl file into the web application's /bin folder. Once I did this I was able to use the ADO.NET Entity Data Model as my data access and isolate the data access to the data layer. The issue with this is that if you change your model you have to manually copy the files to the web application's /bin folder again. I'm hoping that I'm either just not doing something right or this is just because it is still Beta and will be easier in the final release.
 
I also found that quite a few code samples I came across had the Using ... End Using notation around their entity code; however anytime I returned a generic list, for example IEnumerable(Of T), from the data layer to another layer the connection would be lost and I would get an error that said:
 
A connection string must be set on the connection before attempting the operation.
 
It wasn't apparent to me what the problem was initially but then I took out the Using ... End Using notation and everything worked perfect. The End Using code from the code samples was forcing certain objects to be destroyed, including the connection.
 
I've tested retrieving data, adding data, deleting data, updating data, filtering data using standard LINQ syntax as well as Lambda Expressions and binding IEnumerable(Of T) data to a GridView.
 
So aside from the minor annoyances I ran into along the way I now have a working project that uses the ADO.NET Entity Data Model.

Thursday, May 1, 2008

Temporary Tables and Dynamic SQL

                No comments   
When programming in SQL sometimes you need to generate temporary tables inside a stored procedure. That's pretty straight forward with code like this:
 
SELECT     TrackingGroupID,
               Tag
INTO        #temp
FROM       MyTable
 
And you can access #temp in the rest of your stored procedure and drop the temp table when complete.
 
-- testing
SELECT COUNT(*) FROM #temp
 
-- drop temp table
DROP TABLE #temp
 
But if you ever have a situation where you also need to use dynamic SQL you will likely have scope issues. In my particular case I ran into a situation where I needed to apply a dynamic filter to a query and store that data in a temporary table so I could access it after the filter was applied and do further manipulation and analysis and grouping with the data.
 
Note: Typically when I work with temp tables I try to use tables-variables like so:
 
-- create temp table
DECLARE @Temp TABLE
(
     TrackingGroupID  INT,
     Tag                    VARCHAR(50) NOT NULL DEFAULT ''
)
 
However, in this case I was not able to get a table-variable to work. So I ended up using a #temp table instead.
 
My first attempt looked something like this:
 
-- declare variables
DECLARE @SQL VARCHAR(8000)
 
-- apply filter
SET @SQL = 'SELECT      TrackingGroupID,
                                   Tag
                   INTO         #temp
                   FROM        MyTable
                   WHERE       1=1 ' + @Where + '
                   GROUP BY  TrackingGroupID, Tag'

-- apply filter
EXEC(@SQL)

-- do further manipulation or analysis on #temp table
....

-- drop temp table
DROP TABLE #temp
 
But I received this error:
 
Msg 208, Level 16, State 0, Line 18
Invalid object name '#temp'.
 
I knew it was a scoping issue, but I wasn't sure how to work around it exactly. Researching on Google I came across this article:
 
 
"The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed."
 
So I modified my code like so:
 
-- create temp table
CREATE TABLE #temp
(
     TrackingGroupID  INT,
     Tag                    VARCHAR(50) NOT NULL DEFAULT ''
)
 
-- declare variables
DECLARE @SQL VARCHAR(8000)
 
-- apply filter
SET @SQL = 'INSERT INTO #temp
                        (TrackingGroupID, Tag)
                        SELECT       TrackingGroupID,
                                         Tag
                        FROM         MyTable
                        WHERE       1=1 ' + @Where + '
                        GROUP BY  TrackingGroupID, Tag'
 
-- apply filter
EXEC(@SQL)
 
-- do further manipulation or analysis on #temp table
....
 
-- drop temp table
DROP TABLE #temp
 
Now I had access to the #temp table throughout the scope of the stored procedure!
 
I've been told that ##temp might have solved it also since it's global; however, I'm not as familiar with ## so I didn't go down that road.