I finally got around to starting a personal project that I've been meaning to do for quite a while (ironically, a project to help keep me motivated and organized... and it took me forever to get started) and after some thought I decided to make it fairly simple (1 tier, ASP.NET hitting the SQL DB) and get some experience with the whole SQL database embedded into the web project.
Instead of writing out a Data Layer, I started messing around with the LINQ to SQL class builder. This is what I've learned so far:
1. It would be very nice if you could just point it at some database schema and have it build up the class diagram for you. This would be so nice, I suspect it exists, but I haven't been able to find it. Instead you wind up creating the database schema and then creating your object graph to mimic it. It doesn't HAVE to mimic it exactly, which is right and good, but in a lot of cases it will, so why not just build it up for me?
2. Join syntax is weird, strange and hard to grok. I spent quite a while trying to figure out how to do a join on multiple things... e.g. 2 parent tables and 1 child table. The secret was to use anonymous types for the conversion. So something like this:
from p1 in ctx.Parent1
join users in ctx.UserTable on myCurrentUserName equals users.UserName //Just a variable comparison here
join child in ctx.ChildTable on new { UserId = users.UserId, ParentValue = p1.ParentId } equals new { UserId = child.UserId, ParentValue = child.ParentValue }
This struck me as VERY VERY strange and not very well documented, as near as I could find. Why not support boolean operators in the "join ... on" syntax? I'm sure there's a good reason, but I'll be damned if I know what it is! (ps: Please pardon that code if it's not quite right... it's not a copy paste since I'm not on the machine with the code, but that's the basic approach )
Beyond these basic things, it's really really nice to not have to worry about stored procs, permissions, etc. I haven't got the project done yet and I haven't really started mutating data yet, but I'll post more when I know more. I just had to post something to keep my word!
