Linq查詢連接guid與varchar字段

  • 2019 年 10 月 10 日
  • 筆記

使用場景

在數據庫設計中進場會出現一些通用表，如通用附件表，一般都是通過ForeignTable（關聯的表名）和ForeignKey（關聯表的主鍵）與其他表關聯。這樣的表在數據庫中沒有外鍵關係，而且一般ForeignKey的類型是varchar，為了兼容其他表的主鍵可能不一樣。這樣在Linq查詢的時候就不能直接關聯了，如下代碼會編譯不通過：

from a in db.WorkflowInstance  join b in d.xxx//xxx.ID為guid類型  on new { a.ForeignTable, a.ForeignKey } equals new { ForeignTable = nameof(xxx), ForeignKey = b.ID }  select a;

因為xxx.id是Guid(uniqueidentifier)類型和WorkflowInstance.ForeignKey是string(varchar)類型。就算是強行把xxx.id轉成string類型，編譯通過了運行也會報錯，如下：

from a in db.WorkflowInstance  join b in d.xxx//xxx.ID為guid類型  on new { a.ForeignTable, a.ForeignKey } equals new { ForeignTable = nameof(xxx), ForeignKey = b.ID+"" }  select a;

以為這段代碼最終都會轉成sql語句，而Guid是不能直接轉換成varchar的。

解決方案

如果xxx.id是數字類型(int,float,double,decimal)是可以使用SqlFunctions.StringConvert(xxx.id)轉換成string類型，這樣就可以了,SqlFunctions.StringConvert支持double和decimal，基本上數字都可以轉換成這兩種類型，但是注意下轉換時小數點後0的個數，因為string比較時少一個0是不一樣的。 但是Guid不行，因為沒有對應的函數。通過面向百度編程，微軟爸爸給我們提供了一個解決方案：自定義函數。相當於我們自己實現一個SqlFunctions.StringConvert()。

首先在數據庫定義一個轉換函數

if EXISTS(select * from dbo.sysobjects where id = object_id(N'[dbo].[ConvertGuidToChar]') and xtype in (N'FN', N'IF', N'TF'))  drop function [dbo].ConvertGuidToChar  GO  CREATE FUNCTION ConvertGuidToChar  (      @id UNIQUEIDENTIFIER  )  RETURNS VARCHAR(50)  AS  BEGIN      RETURN CONVERT(VARCHAR(50),@id)  END

把函數添加到db模型

可以直接編輯edmx模型文件添加如下代碼：

<Function Name="ConvertGuidToChar" ReturnType="varchar" Schema="dbo" >    <Parameter Name="id" Mode="In" Type="uniqueidentifier" />  </Function>

也可通過從數據庫更新模型添加

添加自定義函數對應的方法

/// <summary>  /// sql函數Guid轉varchar  /// </summary>  /// <param name="id"></param>  /// <returns></returns>  [EdmFunction("iLISModel.Store", "ConvertGuidToChar")]  public static string ConvertGuidToChar(Guid id)  {      throw new NotSupportedException("Direct calls are not supported.");  }

Linq中使用自定義函數轉換類型

from a in d.WorkflowInstance  join b in d.xxx//xxx.ID為guid類型  on new { a.ForeignTable, a.ForeignKey } equals new { ForeignTable = nameof(xxx), ForeignKey =  SqlFunctionsExtension.ConvertGuidToChar(b.ID) }  select a;

這樣就能正常查詢數據了。 註：codefirst是沒有edmx模型的，但是應該可以通過其他方式添加，我沒試，我隨便說的，你別信啊。

VirMach 便宜 VPS

QNews

QNews