SQL查询到C＃Sql查询字符串 [英] SQL Query to C# Sql query string

问题描述

如何将以下SQL代码转换为C＃查询

  DECLARE   @ TempTab  表（ValueType  CHAR ，Num  INT ）
  INSERT   INTO   @ TempTab   VALUES （'  E'， 0 ）
  INSERT   INTO   @ TempTab   VALUES （'   V'， 1 ）
  INSERT   INTO   @ TempTab   VALUES （'  H'， 2 ）
  SELECT  HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID，HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID，
 HYS_UserEmployeeFieldAccess.EmployeeFieldLabel，（ SELECT  Tem.ValueType  FROM   @ TempTab   AS  Tem 其中​​ Num = MIN（T.Num）） AS  EmployeeFieldAccessType 
  FROM  HYS_UserEmployeeFieldAccess  INNER   JOIN   @ TempTab   AS  T  ON  HYS_UserEmployeeFieldAccess。 EmployeeFieldAccessType = T.ValueType 
  WHERE （UserId  IN （ SELECT  UserRoleId  FROM  HYS_UserRoleLink  WHERE  UserId = 27） OR  UserId = 27）
 <跨度类= 代码关键字>组 <跨度类= 代码关键字> BY  dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID，dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID，DBO。 HYS_UserEmployeeFieldAccess.EmployeeFieldLabel 

解决方案

您可以使用 Linqer 。

Linqer：

Linqer是一个SQL to LINQ转换工具。它有助于学习LINQ并转换现有的SQL语句。

以下是链接： Linqer

  WITH  temptb（ValueType，Num）
  AS （ SELECT  '  E'  AS  ValueType，
  0   AS  Num 
  UNION  
  SELECT  '  V'  AS  ValueType， 
  1   AS  Num 
  UNION  
  SELECT  '  H'   AS  ValueType，
  2   AS  Num 
）
  SELECT  HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID，
 HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID，
 HYS_UserEmployeeFieldAccess.EmployeeFieldLabel，
（ SELECT  Tem.ValueType 
  FROM  temptb  AS  Tem 
  WHERE  Num = MIN（T.Num）
） AS  EmployeeFieldAccessType 
  FROM  HYS_UserEmployeeFieldAccess 
  INNER   JOIN  temptb  AS  T  ON  HYS_UserEmployeeFieldAccess.EmployeeFieldAccessType = T.ValueType 
  WHERE （用户Id  IN （ SELECT  UserRoleId 
  FROM  HYS_UserRoleLink 
  WHERE  UserId =  27 ）
  OR  UserId =  27  
）
  GROUP   BY  dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID，
 dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID，
 dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldLabel 

How to convert Following SQL code to C# query

DECLARE @TempTab  TABLE(ValueType CHAR,Num INT)
		INSERT INTO @TempTab VALUES('E',0)
		INSERT INTO @TempTab VALUES('V',1)
		INSERT INTO @TempTab VALUES('H',2)
		SELECT HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID,HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID,
		HYS_UserEmployeeFieldAccess.EmployeeFieldLabel,(SELECT Tem.ValueType FROM @TempTab AS Tem where Num = MIN(T.Num))AS EmployeeFieldAccessType
		FROM HYS_UserEmployeeFieldAccess INNER JOIN @TempTab AS T ON HYS_UserEmployeeFieldAccess.EmployeeFieldAccessType=T.ValueType
		WHERE (UserId IN(SELECT UserRoleId FROM HYS_UserRoleLink WHERE UserId=27) OR UserId=27 )
		GROUP BY dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID, dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID, dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldLabel

解决方案

You can use Linqer for that.

Linqer:

Linqer is a SQL to LINQ conversion tool. It helps learning LINQ and convert existing SQL statements.

Here is the Link : Linqer

WITH temptb ( ValueType, Num )
AS ( SELECT 'E' AS ValueType ,
0 AS Num
UNION
SELECT 'V' AS ValueType ,
1 AS Num
UNION
SELECT 'H' AS ValueType ,
2 AS Num
)
SELECT HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID ,
HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID ,
HYS_UserEmployeeFieldAccess.EmployeeFieldLabel ,
( SELECT Tem.ValueType
FROM temptb AS Tem
WHERE Num = MIN(T.Num)
) AS EmployeeFieldAccessType
FROM HYS_UserEmployeeFieldAccess
INNER JOIN temptb AS T ON HYS_UserEmployeeFieldAccess.EmployeeFieldAccessType = T.ValueType
WHERE ( UserId IN ( SELECT UserRoleId
FROM HYS_UserRoleLink
WHERE UserId = 27 )
OR UserId = 27
)
GROUP BY dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID ,
dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID ,
dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldLabel

这篇关于SQL查询到C＃Sql查询字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！