C# 101 LINQ Samples

http://msdn.microsoft.com/ko-kr/vcsharp/aa336746(en-us).aspx

Restriction Operators

• Where - Simple 1
• Where - Simple 2
• Where - Simple 3
• Where - Drilldown
• Where - Indexed

Projection Operators

• Select - Simple 1
• Select - Simple 2
• Select - Transformation
• Select - Anonymous Types 1
• Select - Anonymous Types 2
• Select - Anonymous Types 3
• Select - Indexed
• Select - Filtered
• SelectMany - Compound from 1
• SelectMany - Compound from 2
• SelectMany - Compound from 3
• SelectMany - from Assignment
• SelectMany - Multiple from
• SelectMany - Indexed

Partitioning Operators

• Take - Simple
• Take - Nested
• Skip - Simple
• Skip - Nested
• TakeWhile - Simple
• TakeWhile - Indexed
• SkipWhile - Simple
• SkipWhile - Indexed

Ordering Operators

• OrderBy - Simple 1
• OrderBy - Simple 2
• OrderBy - Simple 3
• OrderBy - Comparer
• OrderByDescending - Simple 1
• OrderByDescending - Simple 2
• OrderByDescending - Comparer
• ThenBy - Simple
• ThenBy - Comparer
• ThenByDescending - Simple
• ThenByDescending - Comparer
• Reverse

Grouping Operators

• GroupBy - Simple 1
• GroupBy - Simple 2
• GroupBy - Simple 3
• GroupBy - Nested
• GroupBy - Comparer
• GroupBy - Comparer, Mapped

Set Operators

• Distinct - 1
• Distinct - 2
• Union - 1
• Union - 2
• Intersect - 1
• Intersect - 2
• Except - 1
• Except - 2

Conversion Operators

• To Array
• To List
• To Dictionary
• OfType

Element Operators

• First - Simple
• First - Condition
• FirstOrDefault - Simple
• FirstOrDefault - Condition
• ElementAt

Generation Operators

• Range
• Repeat

Quantifiers

• Any - Simple
• Any - Grouped
• All - Simple
• All - Grouped

Aggregate Operators

• Count - Simple
• Count - Conditional
• Count - Nested
• Count - Grouped
• Sum - Simple
• Sum - Projection
• Sum - Grouped
• Min - Simple
• Min - Projection
• Min - Grouped
• Min - Elements
• Max - Simple
• Max - Projection
• Max - Grouped
• Max - Elements
• Average - Simple
• Average - Projection
• Average - Grouped
• Aggregate - Simple
• Aggregate - Seed

Miscellaneous Operators

• Concat - 1
• Concat - 2
• EqualAll - 1
• EqualAll - 2

Custom Sequence Operators

• Combine

Query Execution

• Deferred Execution
• Immediate Execution
• Query Reuse

Utility Routines

• GetProductList code

Join Operators

• Cross Join
• Group Join
• Cross Join with Group Join
• Left Outer Join

연결되지 않은 엔티티는 제거할 수 없습니다.

Attach 메소드로 DataClassesDataContext를 연결 해주면됩니다.

void AttachAll(
	IEnumerable entities,
	bool asModified 
)

Boolean 매개 변수를 false로 설정하여 개체에 타임스탬프(RowVersion)가 없음을 나타냅니다

컬렉션을 업데이트하려면 Attach 대신 AttachAll을 호출합니다.

아래코드는 수정/추가/삭제를 동시에 하고 있습니다.

리스트는 클라이언트에서 웹서비스를 통해 서버측에 전달해주고 있습니다

그럼므로 DataClassesDataContext 와는 연결이 끊겨있습니다.

AttachAll 을 사용하여 Data.DocDataClassesDataContext 가 데이터를 관리하게 지정하는 부분입니다.

   using (TransactionScope ts = new TransactionScope())
            {
                Data.DocDataClassesDataContext dc = new Data.DocDataClassesDataContext(this.dbconn);
                //추가
                dc.GetTable<Contract.Data.Doc.DocCategory>().InsertAllOnSubmit(DocCategoryList.Where(c => c.IsNew == true));

                //수정               
                dc.GetTable<Contract.Data.Doc.DocCategory>().AttachAll(DocCategoryList.Where(c => c.IsEdited == true), true);
                //이부분 주석처리하면 오류발생
				dc.GetTable<Contract.Data.Doc.DocCategory>().AttachAll((DocCategoryList.Where(c => c.IsDeleted == true)));
                dc.GetTable<Contract.Data.Doc.DocCategory>().DeleteAllOnSubmit(DocCategoryList.Where(c => c.IsDeleted == true));

                dc.SubmitChanges();
                ts.Complete();
            }

위의 설명보다 더 자세한 MSDN

http://msdn.microsoft.com/ko-kr/library/bb546187.aspx

여러가지 방법중에 하나이므로 최선 방법 이라고는 할 수 없습니다.!~

코드만 보면 대충 알수있습니다.

  public partial class Address
  {
      public bool IsNew
      {
          get
          {
              return this.Timestamp == null && !this.IsDeleted;
          }
     }
   
      public bool IsDeleted { get; set; }
  }

public static void SaveContact(Contact contact) {
       using (PimDataContext dataContext = CreateDataContext())
      {
          if (contact.ContactID == 0)
          {
              dataContext.Contacts.InsertOnSubmit(contact);
          }
          else
          {
              dataContext.Contacts.Attach(contact, true);
            dataContext.Addresses.AttachAll(contact.Addresses.Where(a => !a.IsNew), true);
              dataContext.Addresses.InsertAllOnSubmit(contact.Addresses.Where(a => a.IsNew));
          }
          dataContext.SubmitChanges();
      }
  }

 

using (NorthwindDataContext context =new NorthwindDataContext())
{          
    context.Log = Console.Out;

    Customer customer = context.Customers.Single<Customer>
                       (c => c.CustomerID.Equals("ALFKI"));
}

또는 디비거에서 확인 해보기

출처 : http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11

실제 디버그에서 출력되는 화면

System.Diagnostics.Debugger: 참조

MyDataContext db = new MyDataContext();

db.Log = new DebuggerWriter();
Here's the code:

using System;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text;

namespace Vandermotten.Diagnostics
{
    /// <summary>
    /// Implements a <see cref="TextWriter"/> for writing information to the debugger log.
    /// </summary>
    /// <seealso cref="Debugger.Log"/>
    public class DebuggerWriter : TextWriter
    {
        private bool isOpen;
        private static UnicodeEncoding encoding;
        private readonly int level;
        private readonly string category;

        /// <summary>
        /// Initializes a new instance of the <see cref="DebuggerWriter"/> class.
        /// </summary>
        public DebuggerWriter()
            : this(0, Debugger.DefaultCategory)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="DebuggerWriter"/> class with the specified level and category.
        /// </summary>
        /// <param name="level">A description of the importance of the messages.</param>
        /// <param name="category">The category of the messages.</param>
        public DebuggerWriter(int level, string category)
            : this(level, category, CultureInfo.CurrentCulture)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="DebuggerWriter"/> class with the specified level, category and format provider.
        /// </summary>
        /// <param name="level">A description of the importance of the messages.</param>
        /// <param name="category">The category of the messages.</param>
        /// <param name="formatProvider">An <see cref="IFormatProvider"/> object that controls formatting.</param>
        public DebuggerWriter(int level, string category, IFormatProvider formatProvider)
            : base(formatProvider)
        {
            this.level = level;
            this.category = category;
            this.isOpen = true;
        }

        protected override void Dispose(bool disposing)
        {
            isOpen = false;
            base.Dispose(disposing);
        }

        public override void Write(char value)
        {
            if (!isOpen)
            {
                throw new ObjectDisposedException(null);
            }
            Debugger.Log(level, category, value.ToString());
        }

        public override void Write(string value)
        {
            if (!isOpen)
            {
                throw new ObjectDisposedException(null);
            }
            if (value != null)
            {
                Debugger.Log(level, category, value);
            }
        }

        public override void Write(char[] buffer, int index, int count)
        {
            if (!isOpen)
            {
                throw new ObjectDisposedException(null);
            }
            if (buffer == null || index < 0 || count < 0 || buffer.Length - index < count)
            {
                base.Write(buffer, index, count); // delegate throw exception to base class
            }
            Debugger.Log(level, category, new string(buffer, index, count));
        }

        public override Encoding Encoding
        {
            get
            {
                if (encoding == null)
                {
                    encoding = new UnicodeEncoding(false, false);
                }
                return encoding;
            }
        }

        public int Level
        {
            get { return level; }
        }

        public string Category
        {
            get { return category; }
        }
    }
}

출처 : http://msdn.microsoft.com/en-us/library/bb549151.aspx

기존의 대리자(델리게이트 사용방법)

using System;

delegate string ConvertMethod(string inString);

public class DelegateExample
{
   public static void Main()
   {
      // Instantiate delegate to reference UppercaseString method
      ConvertMethod convertMeth = UppercaseString;
      string name = "Dakota";
      // Use delegate instance to call UppercaseString method
      Console.WriteLine(convertMeth(name));
   }

   private static string UppercaseString(string inputString)
   {
      return inputString.ToUpper();
   }
}

위의방법을 더 간단하게 하기위해서 Func(T, Treuslt)를 사용

Func을 이용한방법

using System;

public class GenericFunc
{
   public static void Main()
   {
      // Instantiate delegate to reference UppercaseString method
      Func<string, string> convertMethod = UppercaseString;
      string name = "Dakota";
      // Use delegate instance to call UppercaseString method
      Console.WriteLine(convertMethod(name));
   }

   private static string UppercaseString(string inputString)
   {
      return inputString.ToUpper();
   }
}

무기명 메소드에서 사용방법

using System;

public class Anonymous
{
   public static void Main()
   {
      Func<string, string> convert = delegate(string s)
         { return s.ToUpper();}; 

      string name = "Dakota";
      Console.WriteLine(convert(name));   
   }
}

람다식에서 사용방법

using System;

public class LambdaExpression
{
   public static void Main()
   {
      Func<string, string> convert = s => s.ToUpper();

      string name = "Dakota";
      Console.WriteLine(convert(name));   
   }
}

scottgu 블로그:

http://weblogs.asp.net/scottgu/default.aspx

가장 기본적인  셀렉트 방법

LINQ로 쿼리된것을 Person 리스트로 받기

조인해서 가져오기

업데이트 방법

추가

삭제

페이징방법

간단한검색

간단한검색2

평균또는 최고값 가져오기

  툴을 이용해서 서로 고리를 만들어줍니다.

카테고리가 부모개념이니깐 아래와 같이 연결

이걸로 끝~

실제 Linq 에서 사용하는방법은

아래는 CategoryName이 삼숑인것만 가져오는 쿼리 입니다.

    private void Form1_Load(object sender, EventArgs e)
        {
            var Query = from c in dc.Products
                       where c.Category.CategoryName =="삼숑"
                        select c;

            this.dataGridView1.DataSource = Query.ToList();
        }

http://weblogs.asp.net/scottgu/archive/2007/04/08/new-orcas-language-feature-lambda-expressions.aspx

Today's blog post covers another fundamental new language feature: Lambda Expressions.

What are Lambda Expressions?

C# 2.0 (which shipped with VS 2005) introduced the concept of anonymous methods, which allow code blocks to be written "in-line" where delegate values are expected.

Lambda Expressions provide a more concise, functional syntax for writing anonymous methods.  They end up being super useful when writing LINQ query expressions - since they provide a very compact and type-safe way to write functions that can be passed as arguments for subsequent evaluation.

Lambda Expression Example:

In my previous Extension Methods blog post, I demonstrated how you could declare a simple "Person" class like below:

I then showed how you could instantiate a List<Person> collection with values, and then use the new "Where" and "Average" extension methods provided by LINQ to return a subset of the people in the collection, as well as compute the average age of people within the collection:

The p => expressions highlighted above in red are Lambda expressions.  In the sample above I'm using the first lambda to specify the filter to use when retrieving people, and the second lambda to specify the value from the Person object to use when computing the average.

Lambda Expressions Explained

The easiest way to conceptualize Lambda expressions is to think of them as ways to write concise inline methods.  For example, the sample I wrote above could have been written instead using C# 2.0 anonymous methods like so:

Both anonymous methods above take a Person type as a parameter.  The first anonymous method returns a boolean (indicating whether the Person's lastname is Guthrie).  The second anonymous method returns an integer (returning the person's age).  The lambda expressions we used earlier work the same - both expressions take a Person type as a parameter.  The first lambda returns a boolean, the second lambda returns an integer. 

In C# a lambda expression is syntactically written as a parameter list, followed by a => token, and then followed by the expression or statement block to execute when the expression is invoked:

params => expression

So when we wrote the lambda expression:

p => p.LastName == "Guthrie"

we were indicating that the Lambda we were defining took a parameter "p", and that the expression of code to run returns whether the p.LastName value equals "Guthrie".  The fact that we named the parameter "p" is irrelevant - I could just have easily named it "o", "x", "foo" or any other name I wanted.

Unlike anonymous methods, which require parameter type declarations to be explicitly stated, Lambda expressions permit parameter types to be omitted and instead allow them to be inferred based on the usage.  For example, when I wrote the lambda expression p=>p.LastName == "Guthrie", the compiler inferred that the p parameter was of type Person because the "Where" extension method was working on a generic List<Person> collection.

Lambda parameter types can be inferred at both compile-time and by the Visual Studio's intellisense engine (meaning you get full intellisense and compile-time checking when writing lambdas).  For example, note when I type "p." below how Visual Studio "Orcas" provides intellisense completion because it knows "p" is of type "Person":

Note: if you want to explicitly declare the type of a parameter to a Lambda expression, you can do so by declaring the parameter type before the parameter name in the Lambda params list like so:

Advanced: Lambda Expression Trees for Framework Developers

One of the things that make Lambda expressions particularly powerful from a framework developer's perspective is that they can be compiled as either a code delegate (in the form of an IL based method) or as an expression tree object which can be used at runtime to analyze, transform or optimize the expression. 

This ability to compile a Lambda expression to an expression tree object is an extremely powerful mechanism that enables a host of scenarios - including the ability to build high performance object mappers that support rich querying of data (whether from a relational database, an active directory, a web-service, etc) using a consistent query language that provides compile-time syntax checking and VS intellisense.

Lambda Expressions to Code Delegates

The "Where" extension method above is an example of compiling a Lambda expression to a code delegate (meaning it compiles down to IL that is callable in the form of a delegate).  The "Where()" extension method to support filtering any IEnumerable collection like above could be implemented using the extension method code below:

The Where() extension method above is passed a filter parameter of type Func<T, bool>, which is a delegate that takes a method with a single parameter of type "T" and returns a boolean indicating whether a condition is met.  When we pass a Lambda expression as an argument to this Where() extension method, the C# compiler will compile our Lambda expressions to be an IL method delegate (where the <T> type will be a Person) that our Where() method can then call to evaluate whether a given condition is met.

Lambda Expressions to Expression Trees

Compiling lambdas expressions to code delegates works great when we want to evaluate them against in-memory data like with our List collection above.  But consider cases where you want to query data from a database (the code below was written using the built-in LINQ to SQL object relational mapper in "Orcas"):

Here I am retrieving a sequence of strongly typed "Product" objects from a database, and I am expressing a filter to use via a Lambda expression to a Where() extension method. 

What I absolutely do not want to have happen is to retrieve all of the product rows from the database, surface them as objects within a local collection, and then run the same in-memory Where() extension method above to perform the filter.  This would be hugely inefficient and not scale to large databases.  Instead, I'd like the LINQ to SQL ORM to translate my Lambda filter above into a SQL expression, and perform the filter query in the remote SQL database.  That way I'd only return those rows that match the query (and have a very efficient database lookup). 

Framework developers can achieve this by declaring their Lambda expression arguments to be of type Expression<T> instead of Func<T>.  This will cause a Lambda expression argument to be compiled as an expression tree that we can then piece apart and analyze at runtime:

Note above how I took the same p=>p.LastName == "Guthrie" Lambda expression that we used earlier, but this time assigned it to an Expression<Func<Person, bool>> variable instead of a Func<Person,bool> datatype.  Rather then generate IL, the compiler will instead assign an expression tree object that I can then use as a framework developer to analyze the Lambda expression and evaluate it however I want (for example, I could pick out the types, names and values declared in the expression). 

In the case of LINQ to SQL, it can take this Lambda filter statement and translate it into standard relational SQL to execute against a database (logically a "SELECT * from Products where UnitPrice < 55").

IQueryable<T> Interface

To help framework developers build query-enabled data providers, LINQ ships with the IQueryable<T> interface.  This implements the standard LINQ extension method query operators, and provides a more convenient way to implement the processing of a complex tree of expressions (for example: something like the below scenario where I'm using three different extension methods and two lambdas to retrieve 10 products from a database):

For some great blog post series that cover how to build custom LINQ enabled data providers using IQueryable<T>, please check out these great blog posts from others below:

• LINQ to Amazon: Part 1, Part 2, Part 3
• LINQ to NHibernate: Part 1, Part 2, Part 3
• LINQ to LDAP: Part 0, Part 1, Part 2, Part 3, Part 4, Part 5

Summary

Hopefully the above post provides a basic understanding of how to think about and use Lambda expressions.  When combined with the built-in standard query extension methods provided in the System.Linq namespace in "Orcas", they provide a really rich way to query and interact with any type of data while preserving full compile-time checking and intellisense. 

By taking advantage of the Expression Tree support provided with Lambdas, and the IQueryable<T> interface, framework developers building data providers can ensure that the clean code that developers write executes fast and efficiently against data sources (whether a database, XML file, in-memory object, web-service, LDAP system, etc).

Over the next few weeks I'll complete this language series covering the new core language concepts from a theoretical level, and then move on to cover some super practical examples of using them in action (especially using LINQ against databases and XML files). 

Modeling Databases Using LINQ to SQL:

Visual Studio "Orcas" ships with a LINQ to SQL designer that provides an easy way to model and visualize a database as a LINQ to SQL object model.  My next blog post will cover in more depth how to use this designer (you can also watch this video I made in January to see me build a LINQ to SQL model from scratch using it). 

Using the LINQ to SQL designer I can easily create a representation of the sample "Northwind" database like below:

My LINQ to SQL design-surface above defines four entity classes: Product, Category, Order and OrderDetail.  The properties of each class map to the columns of a corresponding table in the database.  Each instance of a class entity represents a row within the database table.

The arrows between the four entity classes above represent associations/relationships between the different entities.  These are typically modeled using primary-key/foreign-key relationships in the database.  The direction of the arrows on the design-surface indicate whether the association is a one-to-one or one-to-many relationship.  Strongly-typed properties will be added to the entity classes based on this.  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.  The Product class then has a "Category" property that points to a Category class instance that represents the Category to which the Product belongs.

The right-hand method pane within the LINQ to SQL design surface above contains a list of stored procedures that interact with our database model.  In the sample above I added a single "GetProductsByCategory" SPROC.  It takes a categoryID as an input argument, and returns a sequence of Product entities as a result.  We'll look at how to call this SPROC in a code sample below.

Understanding the DataContext Class

When you press the "save" button within the LINQ to SQL designer surface, Visual Studio will persist out .NET classes that represent the entities and database relationships that we modeled.  For each LINQ to SQL designer file added to our solution, a custom DataContext class will also be generated.  This DataContext class is the main conduit by which we'll query entities from the database as well as apply changes.  The DataContext class created will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we added.

For example, below is the NorthwindDataContext class that is persisted based on the model we designed above:

LINQ to SQL Code Examples

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:

1) Query Products From the Database

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#:

2) Update a Product in the Database

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:

C#:

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.

3) Insert a New Category and Two New Products into the Database

The code below demonstrates how to create a new category, and then create two new products and associate them with the category.  All three are then saved into the database.

Note below how I don't need to manually manage the primary key/foreign key relationships. Instead, just by adding the Product objects into the category's "Products" collection, and then by adding the Category object into the DataContext's "Categories" collection, LINQ to SQL will know to automatically persist the appropriate PK/FK relationships for me. 

C#

4) Delete Products from the DatabaseThe code below demonstrates how to delete all Toy products from the database:

C#:

5) Call a Stored Procedure

The code below demonstrates how to retrieve Product entities not using LINQ query syntax, but rather by calling the "GetProductsByCategory" stored procedure we added to our data model above.  Note that once I retrieve the Product results, I can update/delete them and then call db.SubmitChanges() to persist the modifications back to the database.

C#:

6) Retrieve Products with Server Side Paging

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.

C#: