In a recent large web site that I worked on, we used LINQ for the data access/model.   This definitely caused some pain in places.   What I discovered is just how well-suited LINQ is to scenarios where ad-hoc queries are allowed.   In fact, if you need to call stored procedures for updates, but can use queries for read-only operations, then you are in good shape.   I've discussed this in a previous post, but I had hoped that I would come up with some workarounds.   One problem that lingers is that if you get an entity back, say a Person object, then try to traverse properties, like Person.Parent.FirstName, it tries to issue a query on the Parent object.   Instead, you need to use Person.ParentId (or similar) to get the primary key and issue the associated stored procedure call directly.

I also found a lot of instances where I needed to invalidate an object once it was in the data context's cache.   Those objects may have come from stored procedure calls, but after you call another stored procedure to update them you end up with invalid cached entries.   If you call Refresh on those objects, it simply issues a SELECT query and you're right back in trouble with ad-hoc queries again!   Even if you manually call a stored procedure to load them again, LINQ ignores them since it thinks they are already loaded.   Ideally, I could choose default SELECT stored procedures.   Next best would be able to manually remove objects from the data context.   There just isn't enough control, so I end up needing to resort to hacks like creating a new data context just to get fresh data!

As it stands, I feel like I can't use LINQ again on any project that doesn't allow ad-hoc queries.   It was just too painful!