Problem:
You want (need to because of the
stubborn dba!) to use stored procedures in your next project and would
also like to take advantage of the new features in .Net 3.5 like LINQ
and extension methods, implicitly typed variables etc. I particularly
don't like to use the implicitly typed variables (the new var keyword).
I like strong typed variables better since it makes the code easier to
read. Don't let the name fool you Implicitly Typed Variables in C# 3.5
are still strongly typed because you can't change the type of the
variable once it has been initialized.
LINQ makes it super easy
to work with stored procedures with dragging and dropping the procedure
into the data context designer (I am not going to get into the details
here, so if you do not know click here).
It will automatically generate a method for you with parameters and
everything. The issue is if you are joining two or more tables
together inside your procedure LINQ does not understand this and it
therefore will not return a hierarchal data object (like ad hoc LINQ
calls do). It is not even an object that consists of the types that
are inside your data context representing your tables. Instead it
returns an object of type ISingeResult. This is where the var keyword
comes in as you normally would just use this and move on. I find this
difficult (the ISingeResult) when I am creating my data access layer as
I would like to know what type of object to expect to get back before I
invoke the method.
Solution:
When
calling LINQ stored procedures from your data context object you can
build and return an object whose type already exists in the data
context, like Topic, Reply, Subject or User in the above. This way you
can return familiar types that have meaning and are easy to read.
1: public Topic Get(int TopicId)
2: {
3: Topic Topic =
4: (
5: from T in _DB.GetTopicById(TopicId)
6: select new Topic
7: {
8: TopicId = TopicId,
9: TeamId = T.TeamId,
10: UserId = T.UserId,
11: User = new User
12: {
13: UserId = T.UserId,
14: UserName = T.UserName
15: },
16: SubjectId = T.SubjectId,
17: Subject = new Subject
18: {
19: SubjectId = T.SubjectId,
20: Subject1 = T.Subject
21: },
22: Description = T.Description,
23: Topic1 = T.Topic,
24: Folder = T.Folder,
25: Link = T.Link,
26: GameDateTime = T.GameDateTime,
27: CreatedDateTime = T.CreatedDateTime,
28: PromotedDateTime = T.PromotedDateTime,
29: Total = 1,
30: TotalReplies = T.TotalReplies.Value
31: }
32: ).FirstOrDefault();
33: if (Topic != null)
34: {
35: Topic.Replies.AddRange(GetReplies(Topic.TopicId));
36: }
37: return Topic;
38: }
This method is returning an single Topic object. It is also joining
in a User object and Subject object both of which are child tables to
Topic in the database. The results from the stored procedure is one
row of data from multiple tables, but the code is building a hierarchal
object. The "select" statement in the above code (lines 6-31) is
rebuilding the types of objects that currently exists in our data
context instead of returning an object of type ISingleResult. This
provides more of a user friendly approach to our data layer. The last
part of this method (line 35) is getting all of the Replies for a
Topic. This is a separate step, but this way we can return one
hierarchal Topic object to the caller with out the need to enumerate
via a foreach statement over the data.
1: private List<Reply> GetReplies(int TopicId)
2: {
3: return
4: (
5: from R in _DB.GetRepliesByTopicId(TopicId)
6: select new Reply
7: {
8: ReplyId = R.ReplyId,
9: UserId = R.UserId,
10: User = new User
11: {
12: UserId = R.UserId,
13: UserName = R.UserName
14: },
15: ParentReplyId = R.ParentReplyId,
16: Reply1 = R.Reply,
17: CreatedDateTime = R.CreatedDateTime,
18: TotalUps = R.TotalUps
19: }
20: ).ToList<Reply>();
21: }
This method does the same thing as the example above. It is running
a query that selects 1 to n Reply records from the database. It then
casts the results in to a generic list of type Reply (this class exists
in our data context) instead of a ISingleResult which gets added to the
topic object above in line 35.
This exposes some of the power behind .Net 3.5 and LINQ, although,
most people will not have a need for this solution, but I worked nicely
for me.