Data Access FAQ (二)

这里是ASP.NET Data Access FAQ的第二部分：
 

LINQ

How can I implement a transaction in LINQ?

A: You can use TransactionScope class in LINQ to implement a transaction. It’s a new function in .NET Framework 2.0 to provide an implicit way to implement a transaction. You can use it in LINQ as shown below:

using (TransactionScope scope = new TransactionScope())

{

       try

       {

             ……….   

             ctx.SubmitChanges();

             ……….   

             ctx.SubmitChanges();

       }

       catch (Exception ex)

       {

             Response.Write("Error happens, Transaction class will automaticlly roll back!");

       }

 

       scope.Complete();

}

You need to reference the System.Transactions assembly and add the namespace ‘System.Transactions’. Also, you need to make sure the windows service-“Distributed Transaction Coordinator Service” is running.

Related link: 

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

How can I use left join in LINQ.

A: You can use the keywords “join” and “into” to implement left join in LINQ. Please take a look at following example:

var sel = from u in ctx.Tags

          join p in ctx.ArticlesTags on u.TagID equals p.TagID into UP

          from p in UP.DefaultIfEmpty()

          select new

          {

                 UT = u.TagID,

                 UT1 = u.Text,

                 UT2 = p.Info

          };

What’s the difference between List<T> and IQueryable<T>?

A: You can return LINQ query result as type of both List<T> and IQueryable<T>. But there are some differences between these two types.

List<T> will create a new list object in memory immediately to persist data. If there’re any associations in this table, the related information will be null. But IQueryable<T> will not retrieve the data until you iterate the data source – use foreach, databind, ToList and so on. When there’s an association in this table, the related information will not be null and can be used. Please take a look at following example to understand the differences between them.

// Return List<T> will fail when referring to related UserInfos object

List<User> users = res.ToList<User>();

var ss = users.Where<User>(p => p.UserInfos.ID != 3);

// Return IQueryable<T> will be successful

IQueryable<User> users = res.AsQueryable<User>();

var ss = users.Where<User>(p => p.UserInfos.ID != 3);

How to implement ‘Like’ operation in LINQ just like in SQL script?

A: If you want to implement the ‘Like’ function in LINQ as in SQL script, you can achieve this by following two methods.

First, you can use Contains, StartsWith, or EndsWith method, here is an example to demonstrate how to use them.

var dd = from p in ctx.Users

         where p.email.Contains("xx") || p.userName.StartsWith("xx") || p.userName.EndsWith("xx")

         select p;

Second, you can use SqlMethods class, it contains a method named ‘Like’ which has the same function with ‘Like’ in SQL script.

var dd = (from p in ctx.Users

         where SqlMethods.Like(p.userName, "%Jiang%") && SqlMethods.Like(p.email,"%WWW%")

         orderby p.accountID

         select p).Take(10);

How to query a DataTable using LINQ?

A: LINQ can query the data source which implements interface IEnumerable. This means you need to first call AsEnumerable method on DataTable, and then you can use LINQ to query the data. Here’s a sample:

var nostr = from u in dt.AsEnumerable()

            where u.Field<Decimal>("m").ToString().ToUpper().StartsWith("3")

            select new

                   {

                   MONEY = u.Field<Decimal>("m"),

                   TIME = u.Field<DateTime>("t"),

                   EXT = "Extra Column"

                   };