Back to the "tips" series after a little break ...
One common question I get is about caching of data in LINQ to SQL. It means a lot of different things so let's start with the following baseline: LINQ to SQL was designed to get you objects from the database and to submit the changes back to the database. It is not intended to be a mid-tier caching component. It is not a cache and it does not pretend to be one. However, it does have certain behaviors that are interesting in this regard:
Object identity
If you want to be able to update the objects you retrieve from the database, it helps to avoid multiple copies of the objects. LINQ to SQL maintains an internal "identity cache" to ensure that within the scope of a DataContext, there will be at most one instance of a given type for a given key value. Think of it like a dictionary indexed by the key value (whether single column or composite key) that stores entities. When you enumerate the results of a query, DataContext looks at each row returned by the underlying DataReader, checks if an instance with the same key value already exists in the dictionary. If an entity is found, it throws away the row and just returns you the existing object from the identity cache. (For those who are more curious, you can look at DataContext.cs, IdentityManager.cs in the sources). As described in another post, we don't stomp on your copy while materializing objects (unless you ask for that using Refresh())
Change tracking
After query execution, you are out of the relational world and into the brave world of objects. There the currency for identity is an object reference rather than a key value since APIs take in objects and not rows or key values. So we also maintain a dictionary that can be looked up by the object. Among many other things, it helps us Attach() a new object by starting to track it.
Enough of the internal view. What does it mean for you?
- LINQ to SQL is designed for retrieving and saving objects. Any caching it does is to that end and not for general purpose use you may have. There are other cache components you can look at.
- By and large, DataContext holds a reference to an entity that it materializes for the life of the DataContext and keeps it as is unless you explicitly modify it or Refresh() it.
- You can ask for it by the key value and retrieve it without remote query (this bug is getting fixed in SP1)
- DataContext feels like it has an obsessive possessive disorder. You can't really take away an object from it. It would rather be disposed than give up the precious entities it holds on to. If you don't like the fact that a reference is maitained (i.e. you want to have entities garbage collected), then use the disposable pattern. See this post for more information.
- Over time, the entities in the id cache get more and more stale. Fine for reference data; not OK for data that changes in the database more often.
- The internal caches make DataContext a write-back cache rather than a write-through cache. We write back on SubmitChanges().
-
-
Non-entities don't show up in the internal cache - there is no key for lookup. Most common case of non-entity is a result from a sproc that does not match the shape of an entity.
-
Turning ObjectTrackingEnabled off can give you a small perf boost - may be useful in a read-only app (only).
Dinesh
An unusual subject for this blog that has been mostly about components I have worked on. But the event yesterday was memorable and worth sharing. The event has been covered widely so I won't even try to broadly summarize. Insted I will list what I found interesting. I was lucky enough (one of 2k lottery winners out of maybe 20+k who were interested) to get a seat for in-person viewing.
There is no substitute to hearing from the man himself and his business partner of 28 years (and a friend/associate of 35 years or so) Steve Ballmer. The range of topics chosen was very enlightening - from "How did your parents react to you dropping out" to "What was the biggest screw up and what did you learn from it". Here are three things I took away from it:
Trust and personal networks matter the most
There is no substitute for personal networks and personal trust and gut instinct. For all the talk of globalization, outcome/evidence-based hiring and rewards, creative processes like forming businesses are deeply personal rather than global; intuitive rather than analytical and grounded in optimism/aspirations rather than backward looking statistics. Maybe the takeaway suits my personality profile but I am always uneasy about decision making processes that try to reduce human judgement to an automaton. SteveB was a known quantity, his passion and intelligence was vetted long before there was any Microsoft, let alone a job opening.
How others see you differs from your self-model
Peronal motivations and self-image can be very far apart from the motivations attributed by others to you and their image of you. This gap is very important to understand. I was not an MS employee during the major anti-trust proceedings. But after hearing from BillG and SteveB about their mindset and approach and after hearing quixotic evil intentions attribute to my team and I during the last few years, I think the aforementioned gap explains a lot.I won't even bother talking about our good intentions. That is too touchy feely. But from what I have seen, my colleagues in various teams and I are so preoccupied with building what we are building and getting it out the door (before it gets killed for some bizarre internal reasons :-) ) that we have no time or smarts left to even think about doing any sinister things. Let alone plan for it years in advance and execute on it to perfection. Heck, like software engineers everywhere, we have a tough time estimating and meeting the estimates for the most basic product/component we would like to ship.
Decisions are based on aspiration and hope rather than just analytics
There is no shortage of blog posts or news items calling for radical strategy/personnel/... change. Most wonder why those in the position of power cannot see what the numbers and facts show. A lot is based on intuition about strategy/leadership/vision that can be counter to facts on the ground at a particular point in time. That doesn't validate the intuition or negate the facts. It simply means that it is futile to look for the fact-based-decision making automaton. Hear the vision, strategy and then lead. follow or get out based on your personal judgement.
Like the takeaways, my process of arriving at them was deeply personal. Take it (or not) FWIW.
Dinesh
A little break from my "LINQ to SQL tips" series of posts. A recent vote of no confidence on a related component orchestrated by community activists reminded me of many questions I have fielded and how the design team approached the design of LINQ to SQL (and also core LINQ APIs and C# language changes for LINQ). Nah, that’s for another day when it is cloudy and raining. Instead, let’s talk about my recent dream. Or rather, a nightmare!
But first turn off your flame throwers, grab a cup of coffee and don't take this too seriously ...
I had this Q&A nightmare about the component I worked on - LINQ to SQL. I am the "expert" providing the non-answers.
Q: How do I use blah pattern with LINQ to SQL (e.g. blah = ActiveRecord if you don't like abstract concepts)
A: You don't!
Q: I think I wasn't sufficiently clear. I stood on one leg and when the phase of the moon was 64% of full, it worked but now that the moon is waxing further, your foo method throws bar exception when I do baz. How do I just get that bit working with LINQ to SQL.
A: You don't
Q: (By now quite upset) Do you even understand blah?
A: (Forced to be less terse) Yes. We considered blah and decided against it for a set of reasons listed below. That pattern is not consistent with the core design assumptions and recommended usage patterns with LINQ to SQL.
Q: (Now a full-force verdict) I hearby find you guilty of violating the implicit agreement to solve the world hunger problem using blah methodology. Hence, what you produced is useless, evil and must be stopped at once. Any software built using your component will accelerate global warming and cause all glaciers to melt at once. And of course, it will irreparably damage the young and impressionable minds of generations of developers leaving them utterly useless for anything except writing some old fashioned code.
A: Thank you for your interest in LINQ to SQL err blah.
Nightmare aside, (what) were we thinking? Stay tuned for that ...
P.S.
1. This release includes backward-looking statements intended to qualify for the safe harbor from liability established by the Public Flagellation by Community Act of 2008. These backward-looking statements generally can be identified by phrases such as "did", "was", "thought" ... and by the absence of "will" "fix" "in future release".
2. I just wanted to get you objects from the table. I swear. Nothing more than that!
3. Scott B., if you are reading this, peace! I won't let you drag me on to the stage at another PDC BoF and I won't fix anything either. I can't. I don't drive LINQ to SQL anymore. I just drive righteous developers crazy J
The code generated for LINQ to SQL is a set of partial classes - one for your DataContext and one per entity mapped to a table or a view. That means you have the opportunity to augment the generated code with additional code in your partial class. I often get questions that have a simple answer in the following list. The code you write could be for:
- Adding unmapped fields or properties to an entity class (e.g. computed property - amount for an Order class based on price, quantity, freight, taxes etc.)
- Providing an implementation of a validation method defined as a partial method (e.g. for a mapper property called "Name", OnNameChanging(), OnNameChanged() or for an entity OnValidate() etc)
- Providing an implementation of CUD override methods supported by the DataContext (e.g. InsertOrder, UpdateOrder, DeleteOrder). You may choose to call a method wrapping a CUD sproc in the method body and/or do more customization.
- Providing an implementation of relationship load override method (e.g. LoadOrders(Customer cust), LoadCustomer(Order ord) etc.). Again, you may choose to call a method wrapping a query sproc in the method body and/or do more customization.
- Add a method to call a sproc returning multiple results (see this previous post for more details)
- Adding utility methods to entity classes or your DataContext class
That sounds like a good bunch of reasons. But I often get a followup question - is there an easy way to do this in the designer? Of course, you can add a new class to a project anytime but a somewhat stylized way is to right click on the design surface and click "View code" (don't ask me why that name was chosen for creating a new partial class). If I do that for Northwind.dbml (file opened in the designer), I get a Northwind.cs at a peer to Northwind.designer.cs.
The file already contains a little class wrapper. It is not a big deal to add that by hand using code snippets but it is another little bit we could do for you (and did in this case).
Dinesh
June 16 addendum: Steve has rightly pointed out that the behavior is different in website projects. My colleague Young Joo - PM for the designer explained the rational as follows "We disabled View Code in website project since it actually has a different meaning than other project types. The View Code option from Dataset Designer within Windows Application project, for instance, creates the partial class file. However, it opens .XSD file in the editor in website project."
So in website project, you will need to resort to the good old way of adding a class through the solution explorer. Thanks Steve!
The common guidance for updates is to keep the original values in view state so that you can recreate the object in its original state, Attach() it and then set the values that the user has modified. This works well for the most part. But at times, a user is allowed to to set only certain values (e.g. Product.UnitsInStock) and is allowed to set them without fear of concurrent change (i.e. optimistic concurrency conflict).
There are two parts to the generated SQL update command:
- SET clause: This is always minimal; i.e. only the changed columns are included by comparing original and current versions of the object. Hence, all properties don’t have to be set
- WHERE clause: By default, L2S (designer or SqlMetal) assume that all columns participate in opt concurrency and generates a full WHERE clause. What you seem to want in the example below is turning off (or limiting) optimistic concurrency. That option is available but tedious in the designer. For each class member, UpdateCheck can be set to “Never” in the property grid. This is not possible through SqlMetal (unless there is a column of timestamp type in which case, that is the only column used in opt concurrency check)
The code below _simulates_ what you would do with just a few (or one) values available in post-back. Please see this post before using Attach() in a simple 2-tier app. Here, I manually set UpdateCheck to Never for all Product class members one by one in the designer. As a result, the update for UnitsInStock succeeded even though I don’t have the original values.
using (NorthwindDataContext db = new NorthwindDataContext(@"C:\Northwind\Northwnd.mdf"))
{
db.Log = Console.Out;
// simulate minimal product information available by creating a new one
Product prod = new Product();
// ID is essential and cannot be skipped
prod.ProductID = 8;
db.Products.Attach(prod);
// Change just one property
prod.UnitsInStock = 11;
db.SubmitChanges();
}
Of course, you must think through very carefully if you want optimistic concurrency check or not. It depends on the semantics of your application.
As an aside, if the designer only provided a single class level property to turn off UpdateCheck for all members, the above solution would be much more palatable. Right now it is a bit tedious when you use the designer.
The DataContext is a rather careful beast. Once an object is retrieved, the DataContext will not stomp on it if a query returns the same object again. This is intentional. Imagine the chaos if you modified some of the retrieved objects or even read the values and made some decision based on that and then the results of a subsequent query just stomped all over that object with newly changed values from the database.
But that does create a separate problem: if you want to get the new values from the database and want your object overwritten, simply querying will not do. DataContext will faithfully execute the queries and bring back the rows with new data for old objects but the rows will be effectively discarded if an object with the same id exists in the cache.
The Refresh() method could be used. It is primarily designed for optimistic concurrency conflict resolution and requires you to state what objects you care to refresh. If you are using it, keep in mind that the changes in the database for a given query can be:
- Newly inserted objects
- Updates of existing objects
- Deleted objects that were present in a previous execution of the same query
Issuing the same query with the same DataContext takes care of the first. Refresh() can handle #2. But #3 requires a little more thought and computation. You could certainly use another DataContext to compare results. But ...
Why is there always a "but"? It is there because if you find your design getting too complicated, it is time to revisit the problem and question the scenario. Are you really using the DataContext lifetime wisely? Why are you keeping the DataContext instance for so long that you are getting into these issues? Is it time to toss the old DataContext instance and create a new one (e.g. to periodically recycle reference data - maybe once every n hours)? Is it time to use two different instances for reference data vs transactional data?
I can't answer these questions for your app. But if you think about them, you will likely have a better design. And maybe you won't need a query refresh! In the design of LINQ to SQL, we often pulled back from complex designs when we felt the designs were way off the main road wandering in the woods. More about that design philosophy followed in the C# design meeting some other day. Meanwhile, hope you find some of the questioning refreshing :-)
Dinesh
Often I get the following questions about stored procs in LINQ to SQL:
- I have sproc that returns multiple results. How can I use it in LINQ to SQL?
- Can I use a sproc that returns results of different shapes; e.g. Customers and Orders?
- I drag-dropped a sproc returning multiple results on the designer surface. But I don't get a method returning multiple results. What is missing?
Here is the overall answer.
Yes, you can use sprocs returning multiple results of different shapes. Here is an example:
This should be added to your partial class that is derived from DataContext:
[Function(Name="dbo.MultipleResultTypesSequentially")]
[ResultType(typeof(Product))]
[ResultType(typeof(Customer))]
public IMultipleResults MultipleResultTypesSequentially()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((IMultipleResults)(result.ReturnValue));
}
In consuming code, here is how it can be used
using(IMultipleResults sprocResults = db.MultipleResultTypesSequentially()) {
List<Product> prods = sprocResults.GetResult<Product>().ToList();
List<Customer> custs = sprocResults.GetResult<Customer>().ToList();
…
}
And no, the designer does not support this feature. So you have to add the method in your partial class. SqlMetal does however extract the sproc. The reason for that is an implementation detail: the two use the same code generator but different database schema extractors.
Anyway, with or without SqlMetal, you can use the feature as described above.
Dinesh
I often get a question along the following lines:
If I remove one of the OderDetails from Order.OrderDetails collection, I see that the reference OrderDetail.Order is set to null but this just orphans the OrderDetail; it is not deleted. How can I change that?
Here is how:
This is not exposed in the designer so you will need to change the generated code (ouch!). In the Association attribute, set the DeleteOnNull property to true as follows:
Once this is set, either of the following operations
ord.Order_Details.Remove(od); // ord is Order; od is OrderDetail
od.Order = null; |
will result in the following being inferred:
// db is an instance of the strongly typed DataContext
db.Order_Details.DeleteOnSubmit(od); |
Please note that this is not the same as cascade delete. Cascade delete is what you specify on your foreign key in the database - for all apps. LINQ to SQL deliberately does not take over this database role. What it does is provide a shorthand notation to say that a particular type is the target of cascade delete constraint in the database (not the source) and hence can be cleaned up when the nullable foreign key is set to null. LINQ to SQL does not by itself provide cascade delete behavior.
This post is a confluence of two distinct sets of comments I got:
- The above-mentioned feature is a well-hidden secret; and
- I post code only in C# while there is a big community of VB users who like LINQ to SQL
So here we go ...
First, the MSDN docs on this topic are a bit too terse and don't describe the purpose of LoadXyz as well as they could. That is perhaps the result of clubbing too many features in one small topic.
Stored procedures can be used for any of the following purposes:
- Queries for objects
- Insert, Update, Delete operations
- Deferred or eager loading of related objects (i.e. indirectly queried objects based on navigation)
The support for the first is quite well known as the designer exposes that feature quite well. Drag and drop a sproc on the right hand pane and you get a method wrapping the sproc. That works for single or multiple results returned.
The support for the second group takes a little more work in the designer as you have to configure the behavior and associate parameters. This is also covered in the documentation topic mentioned above.
That leaves the most interesting feature. Many ORMs support the first two categories above but when it comes to loading of related objects (e.g. Products for a given category object), you often need dynamic SQL. But you don't want to lose the power of the dot just because your DBA does not allow dynamic SQL! You still want to go from a category to its products collection and be able to see the products loaded. Plus ideally this should work for both eager and deferred (or lazy) loading. That is the purpose of the LoadXyz methods. Here is a VB sample that shows how to use them:
In your NorthwindDataContext partial class, just add the following (see comments for assumed sprocs)
' Override loading of Category.Products by using method wrapper. Assume method named CategoryProducts that wraps a sproc
Private Function LoadProducts(ByVal category As Category) As _
IEnumerable(Of Product)
Return Me.CategoryProducts(category.CategoryID)
End Function
' Override loading of Product.Category by using method wrapper. Assume method named CategoryById that wraps a sproc
Private Function LoadCategory(ByVal product As Product) As Category
Return Me.CategoryById(product.CategoryID).Single()
End Function
Now, somecategory.Products and someProduct.Category will work without the need for dynamic SQL and with eager and lazy loading. (BTW, these LoadXyz methods are not intended for top level query - for that all you need to do is call a method wrapping a sproc or write a query that goes against the relevant TVF instead of the table).
I really like this feature, do you?
Update 1 (5/5/08): If you like tricks, not just tips and if you are not faint of the heart, then you must check Matt's latest posting about mockable DataContext. Even if you don't care about TDD, mocking etc., it is a good reminder of how certain mechanisms can be repurposed for new and bizarre (in a good way) goals.
A while ago (October last year), I moved from C# team to the "UI Frameworks" team inside the .NET Developer Platform team. C# team is where LINQ was incubated and large portions of LINQ were productized: LINQ to Objects, LINQ to SQL and C# 3.0 to be precise. So it was with mixed emotions that I left. Why mixed? Because the team and the project that I moved to are also very exciting. Now that I have been there for a while, I can start talking a bit about it though we would rather build something for a preview before I talk :-)
First about the team - the UI Frameworks team drives two big pieces - ASP.NET and client components like WinForms and Silverlight controls. The team covers a fair amount of ground beyond the Data Access Layer all the way to the final presentation of data. This is a very fertile ground for innovation that raises some very interesting topics for future discussions.
LINQ to SQL has been handed off to my previous team - Data Programmability team that owns a variety of ADO.NET technologies. However, given my passion and the years I spent on LINQ and LINQ to SQL in particular, I will likely continue to write about it.
Cheers!
Dinesh
One of the frequently asked questions is about the lifetime of a DataContext. Should it be a long-lived, application-scoped object or should it be a short-lived, request-scoped object? Let's get to the answer by considering the key parameters:
DataContext is ideally suited for a "unit of work" approach: Retrieve a bunch of objects through one or more queries, make changes to the resulting object graph based on user input (databound to controls) or some other request and then call SubmitChanges(). Where applicable, this can be a very efficient usage since all the changes in the unit of work are computed at once and the cost of query (or queries) is amortized over all the CUD operations. This is the case in case of a 2-tier app or in case of an SOA-type app where the service is sufficiently coarse-grained to allow this pattern.
DataContext is also designed for "stateless" server operation: In ASP.NET apps, it is important to minimize state. Plus the only scalable mechanism for maintaining "state" is to serialize it and DataContext is not (by design) serializable. Hence, we spent considerable effort in making DataContext lightweight to construct and disposable. For example, you can use pre-cooked mapping (MappingSource) and cache compiled queries and then use them with a request-scoped DataContext. Here, even the DataContext instances used for a query and a CUD (Create, Update, Delete) operation will be different. This is how it works in case of a web app using LinqDataSource.
With the two "patterns", let's look at some caveats if not antipatterns (YMMV)
Long-lived usage:DataContext does not itself overwrite the objects once you retrieve them through queries. So as time passes, the retrieved objects can become stale if they are frequently changed. Hence, the longer the elapsed time since the query (or queries), the greater the chances of running into an optimistic concurrency exception when you eventually call SubmitChanges(). Of course, how long is too long entirely depends on the characteristics of your data and application. This caveat not very relevant for reference data that is infrequently updated.
Life after SubmitChanges(): DataContext could be used after SubmitChanges() but one has to be careful. SubmitChanges() does all the hard work of figuring out all the changes you have made to the object graph. It orders the CUD operations for you and provides optimistic concurrency check at the granularity of each changed object. However, (by design), it does not do anything about the objects that you have only read but not changed. If those objects have changed in the database, then you have stale data that cannot be easily refreshed. Most applications are quite tolerant of not requiring checking of the "read-set" for submitting changes. However, as time passes, the staleness of "read-set" can be a problem.
In a nutshell,
- It is better to err on the side of shorter lifetime - a unit of work or even a single request for stateless servers are good patterns to start with.
- If you have reference data that doesn't get stale, then by all means consider using a long-lived DataContext instance. Again what is acceptable as "long life" will depend on your app.
- The dominant cost is likely to be queries rather than creation of a new DataContext instance. So use compiled queries and see if you can keep the reference data around. Don't sweat the overhead of creating DataContext instance for making a set of changes unless you have hard data from your app indicating that it is an issue.
- If you want to use a DataContext instance for a long time or for more than one SubmitChanges(), take the time to understand the semantics (described above). It is not the best "default" usage.
- Above all, first think about the correctness or acceptable level of "staleness". Then, see if the DataContext lifetime is even on the critical path for perf. Otherwise, the cost of instantiating a DataContext is not terribly relevant (OK, that is a platitude but if I had a dollar for every attempt at premature optimization I saw, I would be a rich man! Yes, even though dollar buys a lot less these days, premature optimization seems to have become even more plentiful ;-) )
Dinesh
Here is another question I get quite often: I have some common fields (maybe id and timestamp) and some common behavior across all my entities. How do I handle that in LINQ to SQL? After all, the only inheritance mapping supported is Table Per Hierarchy (TPH in ORM jargon). Here is a two part answer:
First, you shouldn't be using mapped inheritance in this case. It is unlikely that you have a single unique, enforced ID-space across all entities (i.e. across all mapped database tables). More likely, you want to encapsulate a pattern and common behavior. The right solution here is an abstract base class that is not mapped. The overridden properties can be mapped in each entity class. Here is a small example based on Northwind database. I have done hand-mapping to keep the example small but you can use SqlMetal to inject a common base class (caution: it is all entities or none, not on a per-entity basis and designer V1 does not support this). You still need to write the abstract base class manually and specify the override property for entity members in the designer.
// Unmapped base class - written manually
abstract class EntityBase
{
public virtual int ID { get; set; }
}
// Mapped derived classes - can be generated using designer/SqlMetal
[Table(Name = "Products")]
class Product: EntityBase
{
int ProductID;
[Column(Name = "ProductID", Storage = "ProductID")]
public override int ID
{
get { return ProductID; }
set { ProductID = value; }
}
[Column]
public string ProductName;
}
[Table(Name = "Orders")]
class Order: EntityBase
{
int OrderID;
[Column(Name = "OrderID", Storage = "OrderID")]
public override int ID
{
get { return OrderID; }
set { OrderID = value; }
}
[Column]
public string CustomerID;
}
// Strongly typed database connection
class Northwind : DataContext
{
public Table<Order> Orders;
public Table<Product> Products;
public Northwind(string s) : base(s) { }
}
namespace BaseClassDemo
{
class Program
{
static void Main(string[] args)
{
//NorthwindDataContext db = new NorthwindDataContext();
Northwind db = new Northwind(@"Server=.\SQLExpress;Database=c:\Northwind\Northwnd.mdf;User Instance=True; Trusted_Connection=True");
db.Log = Console.Out;
var query1 = from o in db.Orders
where o.CustomerID == "AROUT"
select o;
var query2 = from p in db.Products
where p.ID < 10
select p;
// Write out the results of queries using ObjectDumper – available in VS2008 samples directory
ObjectDumper.Write(query1);
ObjectDumper.Write(query2);
}
}
}
The output is:
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [AROUT]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.7
ID=10355 CustomerID=AROUT
ID=10383 CustomerID=AROUT
ID=10453 CustomerID=AROUT
ID=10558 CustomerID=AROUT
ID=10707 CustomerID=AROUT
ID=10741 CustomerID=AROUT
ID=10743 CustomerID=AROUT
ID=10768 CustomerID=AROUT
ID=10793 CustomerID=AROUT
ID=10864 CustomerID=AROUT
ID=10920 CustomerID=AROUT
ID=10953 CustomerID=AROUT
ID=11016 CustomerID=AROUT
ID=11081 CustomerID=AROUT
SELECT [t0].[ProductName], [t0].[ProductID] AS [ID]
FROM [Products] AS [t0]
WHERE [t0].[ProductID] < @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.7
ID=1 ProductName=Chai
ID=2 ProductName=Chang
ID=3 ProductName=Aniseed Syrup
ID=4 ProductName=Chef Anton's Cajun Seasoning
ID=5 ProductName=Chef Anton's Gumbo Mix
ID=6 ProductName=Grandma's Boysenberry Spread
ID=7 ProductName=Uncle Bob's Organic Dried Pears
ID=8 ProductName=Northwoods Cranberry Sauce
ID=9 ProductName=Mishi Kobe Niku
Press any key to continue . . .
I was out on vacation (Zion, Bryce Canyon and Grand Canyon National Parks) so my blog went dark for a while. Also, I noticed a few comments that were incorrectly flagged by the spam filter - I have now published them, albeit after a delay. Sorry about that delay.
As we get closer to a release, it is time to mention a few less known LINQ to SQL (fka DLinq) features that I get emails about. Here is the first in the series about enum mapping. Here is an example based on the northwind database that enhances the usual generated Northwind DataContext and entity classes.
public enum ShippingCompany {
Undefined,
FedEx,
UPS,
DHL
}
// A mini Order class with hand-mapping
[Table(Name="Orders")]
class EnumOrder
{
[Column(IsPrimaryKey=true)]
public int OrderID;
[Column]
public string CustomerID;
[Column]
public ShippingCompany ShipVia;
}
class OrdProj
{
public int OrderID;
public string CustomerID;
public int? ShipVia;
}
class NewNW: NorthwindDataContext
{
public NewNW(): base() {}
public Table<EnumOrder> EnumOrders;
}
class Program
{
static void Main(string[] args)
{
//NorthwindDataContext db = new NorthwindDataContext();
//db.Log = Console.Out;
NewNW db2 = new NewNW();
db2.Log = Console.Out;
var q = from o in db2.EnumOrders
where o.CustomerID == "ALFKI"
select o;
var q2 = (from o in db2.Orders
where o.CustomerID == "ALFKI"
select new OrdProj { OrderID = o.OrderID, CustomerID = o.CustomerID, ShipVia = o.ShipVia }).Distinct();
ObjectDumper.Write(q);
}
}
The output is:
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[ShipVia]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [ALFKI]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.7
OrderID=10643 CustomerID=ALFKI ShipVia=FedEx
OrderID=10692 CustomerID=ALFKI ShipVia=UPS
OrderID=10702 CustomerID=ALFKI ShipVia=FedEx
OrderID=10835 CustomerID=ALFKI ShipVia=DHL
OrderID=10952 CustomerID=ALFKI ShipVia=FedEx
OrderID=11011 CustomerID=ALFKI ShipVia=FedEx
Press any key to continue . . .
You can map an enum to integral type as in this case or to a string if the strings match the enum values (e.g. "FedEx").
There is already so much fictional/scientific/medical/... literature in English so why doesn't everyone in the world just speak English? There are so many dialects and variations of English that one has to often understand. Why bother with another language at all? After all, the purpose of inter-human communication is nicely served by English. What is with all those crazy people who speak other languages - especially when they are related to English (say Indo-European group of languages).
Sorry, I misunderstood the question. You said what? Why are all query languages not just SQL? What is with this LINQ syntax anyway. Annonyingly like SQL but different? How dare you? What were you thinking? Why didn't you use T-SQL/PL-SQL/my-favorite-dialect-of-SQL?
Perhaps I (or others) could answer the question about specific features of LINQ syntax and why certain design choices were made for the minor matter of being consistent with the domain (objects) host programming language(C#/VB) and the environment (CLR, VS IDE). As for the more fundamental question, are you still looking for an answer? Really?
What do I know, I am not a native English speaker or a native SQL speaker for that matter :-). Although I use both the languages extensively and like them both a lot I don;t feel the kind of attachment that would lead to such questions.
Dinesh
I keep getting a lot of questions about whether DLinq is for data objects or for business objects. There is no shortage of advice - both good and bad about what one should and should not do with DLinq.
Here, I want to briefly describe what we had in mind. It might not match someone's specific ideas about the terms "data objects" or "business objects" so hold your flame and send your feedback.
Here is how we approached the design:
- An entity maps to a single table or view and closely matches it in shape. Of course, you can project, change types, add non-persistent members, use inheritance but still, the entity is not a denormalized view of database entities. This helps us keep the updatability story simple. You don't have to be a rocket scientist or computer scientist for that matter to prove that the set of operations is fully contained within the ....
- For queries - LINQ provides punch through many traditional layers on mid-tier - your where clause on a collection can be sent to the server for execution directly. In this respect this is not like doing data access with a handful of sprocs (even though we try our best to support sproc-only access). If you don't need layers upon layers, don't add them. If your business entities are close enough to database tables/views, just add the logic in partial classes and minimize layers - avoid overengineering on speculation that some day, somewhere, somehow you might need the flexibility. When you actually need something, chances are that the overdesign really doesn't match the need.
- If you are doing significant denormalization and/or aggregation in a business object, LINQ provides enough capability to shape your objects programmatically. Of course, the results may not be updatable but that is a hard problem that is best solved based on kno