DLinq Query Execution and Object Identity - LINQ Tutorials
by David Hayden ( .NET Developer )
I had a chance to play a little more with LINQ and DLinq last night. I have really only scratched the surface on these topics and have yet to get to the complicated topics.
Genre and Movie DLinq Example
I went back to my move collection idea again that I have talked about on and off in a few other posts:
I went ahead and re-created the Movie, Genre, and DataContext objects by hand for kicks. I left off the Attach/Detach and property changing events as they weren't necessary for what I wanted to play with this morning. Here is the code:
///
/// MovieLibrary
///
public class MovieLibrary : DataContext
{
public Table<Genre> Genres;
public Table<Movie> Movies;
public MovieLibrary(string connection):
base(connection) {}
}
///
/// Genre
///
[Table(Name="Genres")]
public class Genre
{
private int _id;
private string _name;
private EntitySet<Movie> _movies =
new EntitySet<Movie>();
[Column(Storage="_id", Name="GenreId",
Id=true)]
public int Id
{
get { return _id; }
set { _id = value; }
}
[Column(Storage="_name")]
public string Name
{
get { return _name; }
set { _name = value; }
}
[Association(Storage="_movies",
OtherKey="GenreId")]
public ICollection<Movie> Movies
{
get { return _movies; }
set { _movies.Assign(value); }
}
public Genre() {}
}
///
/// Movie
///
[Table(Name="Movies")]
public class Movie
{
private int _id;
private int _genreId;
private string _name;
private EntityRef<Genre> _genre;
[Column(Storage = "_id",Name="MovieId",
Id = true)]
public int Id
{
get { return _id; }
set { _id = value; }
}
[Column(Storage = "_genreId")]
public int GenreId
{
get { return _genreId; }
set { _genreId = value; }
}
[Column(Storage = "_name")]
public string Name
{
get { return _name; }
set { _name = value; }
}
[Association(Storage="_genre",
ThisKey="GenreId")]
public Genre Genre
{
get { return _genre.Entity; }
set { _genre.Entity = value ; }
}
public Movie() {}
}
DLinq Object Identity
According to the documentation, DLinq assures that the same object will only exist once in memory for each DataContext:
“... the DataContext manages object identity. Whenever an a new row is retrieved from the database it is logged in an identity table by its primary key and a new object is created. Whenever that same row is retrieved again the original object instance is handed back to the application. In this way the DataContext translates the database’s concept of identity (keys) into the language’s concept (instances). The application only ever sees the object in the state that it was first retrieved. The new data, if different, is thrown away. “
Interesting stuff. I did a quick test on this as follows:
MovieLibrary library =
new MovieLibrary(connectionString);
// Ask for SciFi Genre
Genre sciFi = library.Genres.
Single(c => c.Name.Equals("SciFi"));
// Ask for SciFi Genre Again
Genre sciFi2 = library.Genres.
Single(c => c.Name.Equals("SciFi"));
// Will This Change sciFi instance as well?
// Could do ReferenceEquals, too..
sciFi2.Name = "Test";
Essentially when I changed the sciFi2.Name property to “Test“ it also changed the sciFi.Name property to “Test“, which means they are indeed the same object. When I requested the object a 3rd time:
Genre sciFi3 = library.Genres.
Single(c => c.Name.Equals("SciFi"));
sciFi3.Name is “Test“, too, which means this object is definitely coming from the identity table.
However, the query against the database was run 3 different times according to SQL Profiler. Therefore, there was no query execution savings. The documentation suggests DLinq would try to avoid the query against the database if possible, but it does not look like it could in this instance. This may be a question for the LINQ Forums.
DLinq Query Execution
When you create a query as such:
var q = from c in library.Movies
where c.GenreId == sciFi.Id
select c;
The query against the database is not run at this point. This is like creating a DbCommand Object. You can create a DbCommand Object and not execute it against the database. It is not until you enumerate the query object that it is executed against the database. In fact, if you enumerate through it twice, it will make two calls to the database:
Genre sciFi = library.Genres.
Single(c => c.Name.Equals("SciFi"));
// Query Not Executed Here
// Like Creating DbCommand Object
var q = from c in library.Movies
where c.GenreId == sciFi.Id
select c;
// Query Executed Here When Enumerated
foreach (Movie movie in q)
{
Debug.WriteLine(movie.Name);
}
// Query Re-Executed - Ouch!
foreach (Movie movie in q)
{
Debug.WriteLine(movie.Name);
}
If you need to enumerate the collection multiple times, better to use it like such:
Genre sciFi = library.Genres.
Single(c => c.Name.Equals("SciFi"));
// Query Not Executed Here
// Like Creating DbCommand Object
var q = from c in library.Movies
where c.GenreId == sciFi.Id
select c;
// Query Executed Once and Only Once
var list = q.ToList();
foreach (Movie movie in list)
{
Debug.WriteLine(movie.Name);
}
foreach (Movie movie in list)
{
Debug.WriteLine(movie.Name);
}
DLinq Queries - Subtle Difference
Interesting how even though a query is logically the same as another, the SQL generated against the database can be different. Let's take 2 queries that are logically the same:
var q = from c in library.Movies
where c.GenreId == sciFi.Id
select c;
var q = from c in library.Movies
where c.Genre.Id == sciFi.Id
select c;
In both cases we are asking for all movies in the SciFi genre, but in the second case we are navigating into the Genre Class and getting the Genre Id from it. The SQL is different:
exec sp_executesql N'SELECT [t0].[MovieId] AS [Id],
[t0].[GenreId], [t0].[Name]
FROM [Movies] AS [t0]
WHERE [t0].[GenreId] = @p0', N'@p0 int', @p0 = 2
exec sp_executesql N'SELECT [t0].[MovieId] AS [Id],
[t0].[GenreId], [t0].[Name]
FROM [Movies] AS [t0], [Genres] AS [t1]
WHERE ([t1].[GenreId] = @p0) AND ([t1].[GenreId] =
[t0].[GenreId])', N'@p0 int', @p0 = 2
The second query involves the Genres table, which is truly unnecessary in this instance.
Conclusion
LINQ and DLinq are interesting to play with as they help you understand more about the challenges of O/R Mapping and the intricacies of mapping LINQ to T-SQL.
You can view all my DLinq Tutorials as well as LINQ Tutorials.
Source: David Hayden ( .NET Developer )
Filed: LINQ