[+/-] : Linq Where in 進階版

之前的文章有介紹
http://lovetoknowgoogleandmstech.blogspot.com/2009/01/linq-where-in-and-not-in.html

現在來個進階版囉!直接看Code比較快
先來個String字串的Where in

     Dim Lot() As String = Split("21003144,21003145", ",")
     Dim i1 As Integer = 0
     Dim query = From p In db.Packing_Item_Entry_History _
                 Where (Lot).Contains(p.pieh_Lot_No) _
                 Select p.pieh_Lot_No, p.pieh_pih_Job_No
     DataGridView1.DataSource = query
or
     Dim query = From p In db.Packing_Item_Entry_History _
                 Where (Split("21003144,21003145", ",")).Contains(p.pieh_Lot_No) _
                 Select p.pieh_Lot_No, p.pieh_pih_Job_No
     DataGridView1.DataSource = query

再來個數字版的Where in

Use integer
       Dim dkey_arr() As String = Split("14472,14473", ",")
       Dim iArray(dkey_arr.Length - 1)
       For i As Integer = 0 To dkey_arr.Length - 1
           iArray(i) = CType(dkey_arr(i), Integer)
       Next
       Dim query = From p In db.Packing_Item_Entry_History _
                   Where (iArray).Contains(p.pieh_dKey) _
                   Select p.pieh_Lot_No, p.pieh_pih_Job_No
       DataGridView1.DataSource = query

或者是

Use integer
       Dim i_List As New List(Of Integer)
       For Each s As String In String.Split("14472,14473", ",")
           i_List.Add(CType(s, Integer))
       Next
       Dim query = From p In db.Packing_Item_Entry_History _
                   Where (i_List).Contains(p.pieh_dKey) _
                   Select p.pieh_Lot_No, p.pieh_pih_Job_No
       DataGridView1.DataSource = query

...
Read more.

[+/-] : LINQ 查日期區間 between 日期 From to 使用VB.NET

昨天遇到一個要使用到LINQ查日期區間的程式

原來就是這麼簡單!
直接來看Code吧!抄回去就能用了

Using db As New TestClassesDataContext()

Dim query = From p in db.Test _
          Where p.CreateDt >= CType(dt_Date_From.Value, DateTime) _
          And p.CreateDt 〈 DateAdd(DateInterval.Day, 1, CType(dt_Date_To.Value, DateTime)) _
          Select p
End Using

搞定~收工...
Read more.

[+/-] : Linq 的ROW_NUMBER() 使用 VB.NET

網路上找到不少文章！可是自已怎麼都試不出來
文章如下網址
http://bentleydavis.com/2008/11/linq-row-number-in-visual-basic-vb-net.html

真的是江湖一點訣~說穿了就不值錢了
依上面例子！想依樣畫葫盧出來！可是就是沒辦法成功
過了三個小時後才看懂！原來作者是用自己産出的STRING陣列來測試
並不是從DB拉資料出來
Public Class IncrementerZeroBased還是一樣不變
但是Linq Query 請改成

      Dim query = From ii In db.Item_Inventory _
                  Select ii
      Dim i As New IncrementerZeroBased
      Return query.ToList().Select(Function(ii) New With {.NO = i.increment, _
                                                .Loc = ii.ii_lc_ID, _
                                                .Qty = ii.ii_Qty})

或是你要這樣寫也可以

      Dim query = (From ii In db.Item_Inventory _
                  Select ii).ToList()
      Dim i As New IncrementerZeroBased
      Return query.Select(Function(ii) New With {.NO = i.increment, _
                                                .Loc = ii.ii_lc_ID, _
                                                .Qty = ii.ii_Qty})

各位客倌有看出來那裡不一樣嗎

沒錯！就是.ToList()
用SQL Server Profiler來觀察結果
只要用了.ToList()就會産出SQL語法
這樣剛好符合原作使用STRING陣列來測試的條件了

如果你照原作有問題的話~可以試試這個方法喔
終於搞定囉...
Read more.

[+/-] : Linq to SQL 三層式架構之商業層使用Using的問題

因為Using and End Using 用習慣了
所以很習慣的在寫Linq to SQL時也不小心用上了
可是在前端展示層一直叫用不到資料
一度懷疑是不是見鬼了
大家先看Code吧

Public Class Item_InventoryBLL

   Public Function Get_Items_Date() As IList
  Using db As New ERPDataClassesDataContext
   Dim query = From p In db.Item_Kinds _
      Select p
   Return query.ToList()
  End Using
   End Function
End Class

正常可用版

Public Class Item_InventoryBLL

   Dim db As New ERPDataClassesDataContext
   Public Function Get_Items_Date() As IList
       Dim query = From p In db.Item_Kinds _
                   Select p
       Return query.ToList()
   End Function
End Class

看出問題了嗎？
使用Using在前端收不到資料喔
大家用時要小心...
Read more.

[+/-] : LINQ Where in and Not in

在LINQ內如何如用in及not in呢
正常SQL語法

Select * From Customers Where City IN ('UK', 'TW')

在LINQ呢?

Dim query = From c In db.Customers Where (New String() {"UK", "TW"}).Contains(c.City) Select c

那NOT IN呢?

Dim query = From c In db.Customers Where Not (New String() {"UK", "TW"}).Contains(c.City) Select c
...
Read more.

[+/-] : LINQ Where Like 的And串接搜尋

簡單的例子:
在做Google搜尋時大家習慣查LINQ空格ASP.NET
轉成SQL語法是
Select * From Google Where Keyword Like '%LINQ%' And Keyword Like '%ASP.NET%'
這段語法大家一定會寫!
可是轉成LINQ呢?
大家一定覺得很簡單

For Each kw As String In Keywords.Trim.Split(" ")
    query = query.Where(Function(k As Google) k.Keyword.Contains(kw))
Next

呵呵!以上是錯誤的示範，編譯會成功
但是無法執行，原因為
Using the iteration variable in a lambda expression may have unexpected results. Instead, create a local variable within the loop and assign it the value of the iteration variable.
筆者查了好久!也有一堆人問為何這樣不行
原來只要加上一行就好了

For Each kw As String In Keywords.Trim.Split(" ")
    Dim Key As String  = kw
    query = query.Where(Function(k As Google) k.Keyword.Contains(Key))
Next

真是江湖一點訣，知道了就很容易...
Read more.

[+/-] : Group By and Order By And Left Join

使用Northwind內的Table Orders

Dim db As New NorthwndDataClassesDataContext()
Dim query = From p In db.Orders() _
   Group By p.CustomerID _
   Into Total = Count(p.OrderID), avg = Average(p.OrderID) _
   Order By CustomerID _
   Select New NorthwndDLL.ddlCustomerID With {.CustomerID = CustomerID, _
                .Total = Total, _
                .avg = avg}

...
Read more.

[+/-] : Dynamic LINQ Gridview Custom Paging

請參考
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

首先把DynamicLinq.vb加到App_Code內
引用

Imports System.Linq.Dynamic
Imports System.Linq.Expressions

    <DataObjectMethod(DataObjectMethodType.Select, False)> _
    Public Function GetOrgAllData(ByVal startRowIndex As Integer, _
                                  ByVal maximumRows As Integer, _
                                  ByVal sortExpression As String) As IEnumerable(Of Organization)
        'If sortExpression = "" Then sortExpression = "OrganizationsID"
        Dim tbl As New DataClassesDataContext
        Dim query = tbl.Organizations _
                    .OrderBy(IIf(sortExpression = "", "OrganizationsID", sortExpression)) _
                    .Skip(startRowIndex) _
                    .Take(maximumRows)
        Return query
    End Function

    <DataObjectMethod(DataObjectMethodType.Select, False)> _
    Public Function GetOrgAllDataCount() As Integer
        Dim tbl As New DataClassesDataContext
        Return tbl.Organizations.Count
    End Function

...
Read more.

[+/-] : LINQ 初體驗

看了一堆的LINQ文章之後，終於忍不住也開始寫起來了
說實在用看的比寫的容易的多，看別人做出來的和自己動手做出來的感覺真的不一樣

LINQ對於SQL語法很熟的朋友來說要轉換一點都不困難
二~三天一定可以上手，這個投資不管是對是錯，至少益多於害吧^^

寫GridView搭配ObjectDatasource時的Custom paging真的超級方便

Dim query = From p In db.Orders _
          Skip (startRowIndex) _
          Take (maximumRows) _
          Select p

但是困難的來了!如果遇到GridView搭配ObjectDatasource時的Custom paging加Sorting時
那真的頭就大了，用Dynamic LINQ可以解決，但是如果你移到BLL層級時，因為是擴充所以不能用Dynamic LINQ只能乖乖的寫Select Case

Select Case sortExpression
  Case "ShipAddress"
      query = query.OrderBy(Function(u As Orders) u.ShipAddress)
  Case "ShipAddress DESC"
      query = query.OrderByDescending(Function(u As Orders) u.ShipAddress)
  Case Else
      query = query.OrderBy(Function(u As Orders) u.OrderID)
      query = query.OrderByDescending(Function(u As Orders) u.Employees.FirstName)
End Select

這篇文章可以定義為
GridView搭配ObjectDatasource時的Custom paging加Sorting的LINQ語法...
Read more.