1,定义数据库
Dim ctx = KikanContext.Current()
2,写sql语句
1 Dim sql1 = "SELECT (MS_TKM.S_E * 10 + MS_TKM.S_B) AS sitenid,count(*) AS count " & _ 2 "FROM (SELECT TOKUSHIN_OYA_CODE,Max(SAISHINSEI_STATUS) AS P1,Max(SAISHINSEI_KBN) AS P2,Max(DELETE_FLG) AS P3 " & _ 3 "FROM T_HHININ GROUP BY TOKUISAKI_CODE, SHOHIN_CODE, TEIBAN_TANKA, KIKAN_TANKA,MAKER_TANKA ,SHINSEI_NENGETU,TOKUSHIN_OYA_CODE) " & _ 4 "JOIN MS_TKM@togo ON TOKUSHIN_OYA_CODE = MS_TKM.TOKCD1 * 1000 + MS_TKM.TOKCD2 " & _ 5 "WHERE P1 = 0 AND P3 = 0 AND P2 = 0 GROUP BY MS_TKM.S_E * 10 + MS_TKM.S_B ORDER BY MS_TKM.S_E * 10 + MS_TKM.S_B"
3,执行
Dim rList1 = (ctx.Database.SqlQuery(Of HSY0230ViewModel)(sql1)).ToList()
其中HSY0230ViewModel是自建的接受数据类型的模型类
Imports System.ComponentModel.DataAnnotations Public Class HSY0230ViewModel <Required> <Display(Name:="件数")> Public Property count As Integer <Required> Public Property sitenid As Integer End Class