text: Over the last few months I wrote a series of blog posts that covered some of the new language features that are coming with the Visual Studio and .NET Framework "Orcas" release. Here are pointers to the posts in my series::
  Once we've modeled our database using the LINQ to SQL designer, we can then easily write code to work against it. Below are a few code examples that show off common data tasks:
  The code below uses LINQ query syntax to retrieve an IEnumerable sequence of Product objects. Note how the code is querying across the Product/Category relationship to only retrieve those products in the "Beverages" category:
  C#:
  VB:
  The code below demonstrates how to retrieve a single product from the database, update its price, and then save the changes back to the database:
  Note: VB in "Orcas" Beta1 doesn't support Lambdas yet. It will, though, in Beta2 - at which point the above query can be rewritten to be more concise..
  C#
  The code below demonstrates how to delete all Toy products from the database:.
  The code below demonstrates how to implement efficient server-side database paging as part of a LINQ query. By using the Skip() and Take() operators below, we'll only return 10 rows from the database - starting with row 200.
  Pingback from Using LINQ to SQL (Part 1) - ScottGu's Blog
  As always, very nice! It would be great if you could continue this series with a discussion of how to handle m:m relationships, given that they are not natively supported by LINQ to SQL. Just some examples how one would work around that limitation in the DAL, if one happens to have a DB with m:m relations.
  If my database have more then 2,000 store procdures ,how can i use DLinq?
  I have a little suggestion here. Is it possible to allow VB statements to span multiple lines without the use of underscores such as
  A.foo(bar) A = B
  some simple (but smart) regex should be able to separate the lines into
  A.foo(bar)
  A = B
  the underscore character are 2 characters away from your little finger on home row and if in the future I'm going to be implementing a lot of LINQ then that would surely slow down my typing speed to some degree
  I posted a number of observations about LINQ to SQL on my blog at
  I would love to know more about in which context the DataContext is used/shared as it doesn't seem thread safe or that it should be around too long because of the caching/stale data problems.
  [)amien
  Wow, I was really wiating for a post on LINQ to SQl from you and today is my day.
  Thanks
  Vikram
  Great write up Scott!
  How does Linq/Sql handle many-to-many with composite-keys?
  Also, in the above situation, if you have a 'join' table:
  ie.
  Customers
  CustomerProducts
  Products
  On a delete and add of products to customers, does it handle the CustomerProducts association table?
  Thanks again
  Scott,
  The thing I'm still not quite getting after these tutorials, and even after playing with it some in the beta is the best way to use this along with ASP.NET Data Controls. Unless I'm missing something, the generated Update(item) method requires the same DataContext instance that created the item, meaning it doesn't work across postbacks when used in an ObjectDataSource.
  I know you've hinted at a LinqDataSource- is there anywhere we can see that yet?
  Pingback from oakleafblog.blogspot.com/.../yet-another-primer-on-new-language.html (see the 5/19/2007 update)
  Thanks for adding the VB sample code!
  --rj
  Hi Davidacoder/Steve,
  I'll put m:m relationships on the list to blog about in one of my future Linq to SQL posts (it deserves a post in and of itself).
  LINQ to SQL doesn't natively support m:m - instead you typically create an intermediate table to handle the m:m relationss.
  LINQ to Entities, which will ship shortly after "Orcas", does support m:m relationships directly. So that is another alternative to consider.
  Hi Daniel,
  I'll post more about integrating LINQ to SQL with ASP.NET data controls shortly.
  Beta2 will have the <asp:linqdatasource> control built-in, which will provide the easiest way to use LINQ with ASP.NET controls (basically just point the ASP.NET controls at the LINQDataSource, and then point the LINQDataSource at the LINQ to SQL entities with a filter and you are done - selection, paging, editing, deleting, insertion all handled for you).
  Alternatively,. It works well with the <asp:objectdatasource> control today.
  Hi Kain,
  You can have any number of stored procedures that you want on your DataContext - so you should be able to use all 2000 of them in your database.
  "LINQ to SQL doesn't natively support m:m - instead you typically create an intermediate table to handle the m:m relations.
  LINQ to Entities, which will ship shortly after "Orcas", does support m:m relationships directly. So that is another alternative to consider."
  Scott, is this going to be a part of the RTM for Linq to Sql ?
  A ORM should at minimal support 1:1, 1:M, M:1, M:M in my opinion.
  I'd be disappointed to find out that I would need another tool to handle M:M
  Could you have an option to handle the DataContext via a 'Open Session in View' model (ie. or here) rather than call attach or need to relate entities (not EDM, but a Poco object) directly to controls?
  Pingback from robinzhong’s blog - links for 2007-05-19
  <asp:linqdatasource> would be nice...easy!
  Will anything ship with Orcas to generate LINQ to SQL from and existing SQL2005 database?
  Any performance comparisons between LINQ and .xsd DataSets?
  System.Data.Linq.dll
  Hi Scott,
  Thanks for the excellent write-up, as usual. Does Linq to SQL replace SQL metal (which was part of the June CTP)?
  Thanks,
  Burton
  Pingback from robinz’s tech life » Blog Archive » links for 2007-05-20 » Enjoying Open Source!
  Hi Kyle,
  The LINQ to SQL designer makes it really easy to model an existing SQL 2005 database. I'll cover this in my next blog post in the series. Basically you can add all of the tables in the database onto the designer and it will automatically infer/create the appropriate associations between the entities.
  In terms of performance, LINQ to SQL is really, really fast. In general I'd expect it to be faster than using a DataSet approach.
  Hi Burton,
  SQLMetal was a command-line tool that creates LINQ to SQL entity models (at the time LINQ to SQL was called "DLINQ").
  In addition to the command-line option, LINQ to SQL now supports the WYSIWYG designer surface I showed in my screen-shot above. I find this more convenient to model data entities, which was why I used that approach.
  Scott
  I am just confused between LINQ tools and Dynamic Data Controls for ASP.NET as explained in AspNet Futures.
  Are they talking about the LINQ tools with a new name comming up with Orcas or its totally different and have no connection with Orcas Tools.
  A few line explanations will clear my doubts.
  SoftMind
  Is it possible to do something like this?
  int count;
  var products = (from p in db.Products
  select p).Skeep(20).Take(10).
  GetTotalRowsCount(out count);
  What type of SQL do the Skip() and Take() operators generate? Would it be efficient enough for paging large amounts of data or would you still want to write your own stored procedure?
  Using LINQ to SQL (Part 1) [Via: ScottGu ] Easier Winform UI Thread Safe Methods with DynamicProxy2...
  Hi Scott,
  Using the LINQ to SQL Designer, can I assign stored procedures for the CRUD operations of my entities, (like in named datasets) or do I have to rely on the generated SQL code?
  Hi Steve,
  Unfortunately LINQ to SQL won't support M:M except via an intermediate table in the "Orcas" V1 timeframe.
  In terms of the "Open Session in View" pattern, I'd probably recommend against doing this. If I understand the second link correctly, this stores things web requests - which ultimately makes scaling out across multiple web servers harder, and can lead to scaling challenges as more users hit the application.
  While you could use this approach with the LINQ to SQL DataContext, a better approach would be to release the context at the end of each request, and use the Attach() feature when a user later posts back and you want to rehydate the entity from the view. This doesn't require anything to be stored on the server, and is really easy to-do (1 line of code). This will work regardless of whether you are in single server or web farm mode, and will scale incredibly well.
  Hi SoftMind,
  The new Dynamic Data Controls in the ASP.NET Futures release are a set of UI control helpers that make it much easier to quickly get data-driven UI up and running.
  The current release of the data controls work directly against the database - but the next release will allow you to work against LINQ to SQL entities. This will make it really easy to define your data entities, and then quickly generate UI based on them.
  Hi Koistya `Navin,
  You can write this code to calculate the total number of rows in the query:
  var query = from p in db.Products
  select p;
  int totalCount = query.Count();
  var subProducts = query.Skip(20).Take(10);
  This will execute two database requests - the first will return the total number of rows in the query. The second returns the subset of rows you want.
  At now point does the entire set of products get fetched to the web-server.
  Hi Matt,
  LINQ to SQL uses the new ROW_NUMBER() function with SQL 2005 to implement efficient paging support within the database.
  I posted two blog posts last year that talk a little more about this capability:
  weblogs.asp.net/.../434314.aspx
  and:
  weblogs.asp.net/.../434787.aspx
  The benefit of using LINQ to SQL is that you don't need to write a SPROC to achieve this, and it is much easier to write.
  Hi Manuel,
  >>>> Using the LINQ to SQL Designer, can I assign stored procedures for the CRUD operations of my entities, (like in named datasets) or do I have to rely on the generated SQL code?
  Yes - you can assign stored procedures for the update, insert and delete operations of your entities. So if you don't want to rely on the SQL code that LINQ to SQL infers for you you can use these to override it.
  Hi folks,
  you can read this great post here in Spanish:
  thinkingindotnet.wordpress.com/.../usando-linq-to-sql-1%c2%aa-parte
  P.D.: Sorry, I read it late.
  Great article. A really useful primer on LINQ to SQL. A question for you: Do you have an error in the text? Please check these sentences early in the article:
  "For example, the Category class above has a one-to-many relationship with the Product class. This means it will have a "Categories" property which is a collection of Product objects within that category."
  Should that be a "Products" property instead of a "Categories" one?
  -Krip
  >> LINQ to SQL will know to automatically persist the appropriate PK/FK relationships for me
  AMAZING!!
  Pingback from links for 2007-05-21 » mhinze.com
  When will Linq to SQL support other databases (specifically DB2)? Last I read there's only support for MS SQL - doesn't that strike you as rather limiting? Hopefully I'm way off base here, I'm sure you wouldn't plan on shipping a product as cool as this with support only for SQL Server.
  Scott, you said :
  "you can use the Attach() method on Tables to re-attach a disconnected entity to a DataContext. "
  How does Linq-to-SQL know if the re-attached object has been modified or which fields have been modified?
  Do you recommend exposing these generated classes directly in a data contract for a service? I prefer not to expose database layer implementation to the clients. So how can these generated classes be mapped to business objects for the client callers - is it possible to map to and from external classes and still successful re-attach and use the objects across service calls?
  Could you talk a little about the underlying classes that are generated? Are the generated classes partial classes that we could "extend"? Basically, can we add new code to the Products class? Something like a IsNameOk() function inside the Products class that would not map to a field in the database (obviously since it's a function not a property) that would return true or false based on other properties (building business rules is what I'm trying to get to)
  Thanks.
  Link Dump for Monday, May 21, 2007
  Typo:
  This means it will have a "Categories" property which is a collection of Product objects within that category.
  I think "Categories" should be "Products".
  I have some q's too!
  1. Can you do many to many relationships without a class for the (in RDB necessary) many-to-many table?
  2. Can you also call Save on a single object, and maybe even control the cascaded saving of related objects?
  3. In the future articles, can you please also explain scenario's where the database tables and the classes are not so conveniently similar? Some examples: many-to-many relationships and inheritance.
  Thanks!
  PS. This gives .NET the definitive edge over the rest of the field. It's nice to see MS compete based on some pretty innovative ideas.
  Will there be support for any other db besides SQL Server in the RTM? (spec. Oracle)
  The problem with this model is that it allows new developers coming into the field to learn bad practices rather easily. Allowing direct coupling from the presentation tier to the DB. In turn creating my "disposable" apps.
  Hi El Guapo,
  When doing an Attach for disconnected scenarios, you can either use a timestamp column with the database to determine if there have been changes, or do a comparison of the values to see if there are any deltas.
  Hi Peter,
  All of the classes generated by the LINQ to SQL designer are generated as partial classes - which means you can definitely add custom validation logic and additional methods/properties to them. This should make adding business rules much, much easier.
  Hi Mike,
  1) For many to many relationships you need to go through an intermediate class (I'll cover this in a future blog post).
  2) When you Save on the DataContext, it will persist all changes you make (and update everything within a single transaction by default).
  3) I'll cover some of the more advanced modeling/shaping scenarios in a future post. Stay tuned! :-)
  Hi Will,
  There is no need to couple your presentation tier to the database with LINQ. LINQ to SQL generated entity classes that abstract your database and provide a clean way to add validation logic.
  You can also optionally add an intermediate business layer class between your UI and entity layer to add additional separation if you'd like.
  Hi Scott
  Will there be a zoom feature on the Linq to Sql Deisgner for large databases? This would be brilliant.
  Rob Mathieson
  Hi, Scott
  One question, is it possible to use Extension Methods for operators, like: ==,+,-?
  Orlando Agostinho
  Scott, please check this discussion on the namespace naming for LINQ. Some of the biggest advocates of LINQ really can't understand Microsoft's decision here. We could really do with some feedback,
  Thanks, Pete.
  forums.microsoft.com/.../ShowPost.aspx
  > All of the classes generated by the LINQ to SQL designer are generated
  > as partial classes - which means you can definitely add custom validation
  > logic and additional methods/properties to them.
  OK, enough teasing... can we have this thing by tomorrow? :-)
  If people want a bit more on LINQ to SQL, I made a few videos that you can find links to here;
  mtaulty.com/.../9322.aspx
  Or, alternatively, you will also find them up here where they might stream/download a bit better :-)
  Might be of use to some folks.
  Mike.
  Great article,
  Is there any chance of you showing a simple best practice ASP.NET example of a presentation tier, business logic tier and data acess logic tier solution using LINQ and the asp:linqdatasource. I'd be interested to hear more about scalability, how Attach works and where the DataContext objects would be etc...
  Can't wait for the next installment in this series.
  Peter
  Yep - that is definitely on my list todo. :-)
  Could you give an example of a computed column? Does it have the same limitations as ADO.Net's DataColumn.Expression property or can it get it's results from a normal DotNet function with full access to all libraries?
  Any typo in this:
  This means it will have a "Categories" property which is a collection of Product objects within that category.
  Shouldn't it be:
  This means it will have a "Products" property which is a collection of Product objects within that category.
  ??
  SubSonic Vote: Rewriting the WHERE Method?
  hi Dynamic .
  will linq support dynamic query builder.
  if not :
  ---------------------------------
  var query :
  if(nameTextBox.Text.length>0)
  {
  query = from p in db.Products
  where p.Name=nameTextBox.Text
  select p;
  }
  if(ageTextBox.Text.length>0)
  query = from p in db.Products
  where p.Age=ageTextBox.Text
  if...........
  return query ;
  Hi, hawk
  How about this, I don't really sure, but maybe will work;
  query = from p in db.Products
  where p=> p.Name==""+nameTextBox.Text
  || p=> p.Age==""+ageTextBox.Text
  select p;
  Hi, scott, what do you think about this?
  See Ya!
  Lisbon/Portugal
  Will the Linq to Sql designer support some sort of zoom feature for large databases?
  Rob
  In the SP example you put the results of the SP into an anonymous type, but then you use a concrete type to iterate over the collection of anonymous types. How much support can the compiler provide to ensure that the shapes of the objects in the anonymous collection match the shape of the iteration variable?
  Hi Rob,
  Yep - the good news is that the LINQ to SQL designer fully supports a "zoom" feature. So you can put as many tables/entities as you want on it. :-)
  Hi Shane,
  In the SP example above the SP actually returned an explicit type (specifically a sequence of Product objects). This means that the compiler will provide explicit compile-time checking of the result.
  Hi Thomas,
  Yep - you can work with computed columns using LINQ to SQL. For some good examples check out my "anonymous types" post here: weblogs.asp.net/.../new-orcas-language-feature-anonymous-types.aspx
  It's been said multiple times before, but I'll just reiterate; We need Oracle support i LINQ (LING for Oracle?). It kills me to see all this goodness and know that there is just no way we can ever use this... :(
  Great article and I have a request...
  1) Add ParentID to Categories making categories a tree, and show how we can load from the root categories and walk the tree where root categories have a ParentID of zero.
  2) Change the Product Category Relation to a many-to-many and show how Linq to SQL can hide the existance of the relationship table [ProductCategoryRelation] so we just do product.Categories and get the actual Category and not a representation of the relation.
  Thanks...
  I would like to ask how about LINQ Asynchronous? Imagine, I would want running one query but i know that query will be running not so fast, and i want to use any asynchronous mechanism that LINQ can give me!
  Thanks a lot!
  I'll add a tree sample on my list of posts to blog. The good news is that it works well with LINQ to SQL.
  Regarding M:M relationships, LINQ to SQL unfortunately only supports them via an intermediate table/class today. I'll blog how to manage that in a future blog post as well.
  Hi Orlando,
  LINQ itself lends itself very well to asynchronous programming. Because queries with LINQ are executed in a deffered way, you can use a nice programming model to manage this. I'll put it on my list of topics to discuss.
  Your "anonymous types" post only seems to deal with "calculated columns" that derive from database functions.
  Here's an example I found from an old DLinq document that illustrates what I think is very powerful; however, it does not seem to work in Beta 1. What happened to the ToSequence() method (has it been renamed)?
  var q =
  from c in db.Customers
  where c.City == “London”
  select new { c.ContactName, c.Phone };
  var q2 =
  from c in q.ToSequence()
  select new MyType {
  Name = DoNameProcessing(c.ContactName),
  Phone = DoPhoneProcessing(c.Phone)
  };
  Here's a quote from the doumentation that explains what I want to do:
   ToSequence() operator that shifts processing at that point from a DLinq query into a locally executed one."
  "Note that the ToSequence() operator, unlike ToList() and ToArray(), does not cause execution of the query. It is still deferred. The ToSequence() operator merely changes the static typing of the query, turning a Query<T> into an IEnumerable<T>, tricking the compiler into treating the rest of the query as locally executed."
  You've been kicked (a good thing) - Trackback from DotNetKicks.com
  用于SQL的LINQ，LINQ如此的酷，查询，插入，更新，删除，分页！
  In Part 1 of my LINQ to SQL blog post series I discussed "What is LINQ to SQL" and provided a basic overview
  It's so cool,I like it
  But I care the perormance when it is runing under huge data
  Also,I want to know how to fix the complex relation of two or more entities.
  Thanks a lot...
  I actually got a shiver down my spine. A good one though! I can't explain how exciting this new release is!!! :-D
  Linq - Way too easy!
  I would also be very interested in seeing how this would be used in an ASP.NET environment. For example, I don't follow this comment at all:
  "When doing an Attach for disconnected scenarios, you can either use a timestamp column with the database to determine if there have been changes, or do a comparison of the values to see if there are any deltas."
  Compare *what* values? Are you saying to load another instance of the object (current DB values) then compare to the re-connected object? IMO, this is hinting at a pretty serious design issue. Is it the DataContext that's tracking changes instead of the object itself? If so, all of your change tracking and undo functionality goes bye-bye if the DataContext is "lost".
  Pingback from Kaizenlog » Blog Archive » Java 13/06/2007
  How can we return the result of Linq query? Which data type should I use?
  I saw an example that was creating a class representing the database table, so the function that queries the data was returning a generic List to this class type. Are there any different and maybe better way to achieve this?
  I'll second the calls for Oracle support! As an ISV, I have to write apps that run on SQL Server and Oracle (because that's what clients demand). If LINQ to SQL is really LINQ to SQL Server, then it's a technology I can never use.
  Hi Juliano,
  In my language feature posts (especially the query syntax one) I cover the return types of LINQ query expressions. In general, you can treat them as IEnumerable<T> sequences - where the type <T> is based on the select clause of the query expression.
  I'll cover that topic in an upcoming post later this month hopefully.
  Hi all,
  I've just read that many people were wondering how to implement many-to-many relationships with Linq to Sql. Here is one simple possible solution exposed on my blog (source code attached): blogs.msdn.com/.../how-to-implement-a-many-to-many-relationship-using-linq-to-sql.aspx
  Mitsu
  Pingback from What Everyone is Excited About - LINQ « Wilfred Mworia’s Blog
  Last month I started a blog post series covering LINQ to SQL. LINQ to SQL is a built-in O/RM (object
  Hi Soctt,
  You have mentioned that when we use Skip().Take(), it uses the ROW_NUMBER() function in SQL 2005 but when i looked at the query generated i do not see this being used. It's using Sub-query to get the correct set of records.I am using Orcas Beta1.
  Any ideas why this would be so?
  ScottGu has a 3 part post on his blog about LINQ to SQL . This is a very cool technology, which I hope
  Pingback from Hecgo.com » M??s sobre LINQ y Visual Studio 2008
  Some quick links about LINQ: Articles about extension methods by the Visual Basic team Third-party LINQ