C# 2010 using linq

Question

In a C# 2010 desktop application, I am trying to determine how to write the following sql statement via linq to sql:

select COUNT(Tran_ID) as good_count
             from dbo.Trans t            
             inner join dbo.table1 iw on iw.pkg_id = t.pkg_id
             inner join dbo.table2 ip on ip.pkg_id  = iw.pkg_id
             inner join dbo.table3  tt on tt.pkg_id = ip.cust_Number
             where (t.cust_Date is not null) and
             SUBSTRING(ip.item_Number,1,3) = 'UC3'

Thus can you tell me and/or point me to a reference I can use to determine how to write the above statement in linq to sql that connects to a sql server 2008 database?

Answer 1

This link is perfect to understand linq statements : http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

Before answer you, can you tell me if you use a entity framework?

List<Tran> trans = GetTransList(); 
List<Tab1> table1 = GetTable1List(); 
List<Tab2> table2 = GetTable2List(); 
List<Tab3> table3 = GetTable3List(); 

  
Int32 good_count = (from t in trans 
              join iw in table1 on t.pkg_id equals iw.pkg_id
              join ip in table2 on iw.pkg_id equals ip.pkg_id
              join tt in table3 on ip.cust_Number equals tt.pkg_id
              where t.cust_Date != null && ip.item_Number.StartsWith("UC3")
              select t.Tran_ID).Count();

Answer 2

Hi jazz_dog;

This should return what you need.

var good_count  = (from t in Trans
              join iw in table1 on t.pkg_id equals iw.pkg_id
              join ip in table2 on iw.pkg_id equals ip.pkg_id
              join tt in table3 on ip.cust_Number equals tt.pkg_id
              where t.cust_Date != null && ip.item_Number.StartsWith("UC3")
              select new {
                  t, 
                  iw, 
                  ip,
                  tt
             }).Count();

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 3

sorry fernando....when i edited my post, i didn't see your post. (page refresh :) )

Answer 4

I am not using the entity framework. Would I need to use: List<Tran> trans = GetTransList(); ?

Answer 5

Hi jazz_dog;

As Long as Trans, table1, table2 and table3 are collections just use them.

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 6

@Lazzarotto;

Not a problem.

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 7

jazz, i specified 'get list method' only for completeness!