将此SQL查询转换为Linq(不存在+子查询) [英] Convert this SQL query to Linq (Not Exists + sub query)

问题描述

我希望将此SQL转换为LINQ. (应该从输入中基于3列选择表生产中不存在的行.如果两个表中的列均包含NULL，则应将其视为具有相同的值)

I would like this SQL to be converted to LINQ. (it shouldl select rows from input which do not exist in table production based on 3 columns. If a column in both tables contains NULL, it should be considered as having the same value)

SELECT i.* FROM INPUT AS i
WHERE NOT EXISTS
(SELECT p.Agent FROM Production AS p
WHERE ISNULL(i.CustID,'') <> ISNULL(p.CustID,'')
AND ISNULL(i.CustName,'') <> ISNULL(p.CustName,'')
AND ISNULL(i.household,'') <> ISNULL(p.Household,''))

推荐答案

首先-这不是一个好的SQL查询.每列都包装在不可精简的函数中，这意味着引擎将无法利用这些列中任何一个的任何索引(假设您有任何索引).

First of all - this is not a good SQL query. Every column is wrapped in a non-sargable function which means that the engine won't be able to take advantage of any indexes on any of those columns (assuming you have any).

让我们首先将其重写为半体面的SQL查询:

Let's start by rewriting this as a semi-decent SQL query:

SELECT i.*
FROM Input i
LEFT JOIN Production p
    ON (p.CustID = i.CustID OR (p.CustID IS NULL AND i.CustID IS NULL))
    AND (p.CustName = i.CustName OR (p.CustName IS NULL AND i.CustName IS NULL))
    AND (p.Household = i.Household OR
        (p.Household IS NULL AND i.Household IS NULL))
WHERE p.CustID IS NULL

现在已经说了，LEFT JOIN / IS NULL对于效率也不是很好，但是我们在这里没有太多选择，因为我们要在多个列上进行比较.根据您的列名，我开始怀疑架构是否已正确规范化.一个CustID最有可能与一个且只有一个CustName相关联-您必须将这两个都进行比较似乎有点奇怪.还有Household-我不确定这是什么，但是如果它是varchar(x)/nvarchar(x)列，那么我想知道它是否也可能与客户之间存在1:1关系.

Now having said this, LEFT JOIN / IS NULL is not great for efficiency either, but we don't have much choice here because we're comparing on multiple columns. Based on your column names, I'm starting to wonder if the schema is properly normalized. A CustID should most likely be associated with one and only one CustName - the fact that you have to compare both of these seems a bit odd. And Household - I'm not sure what that is, but if it's a varchar(x)/nvarchar(x) column then I wonder if it might also have a 1:1 relationship with the customer.

如果我在这里推测过多，请随时忽略此段；但以防万一，我想说的是如果数据没有正确归一化，将其归一化将使查询变得更加容易和快捷:

If I'm speculating too much here then feel free to dismiss this paragraph; but just in case, I want to say that if this data isn't properly normalized, normalizing it would make it much easier and faster to query on:

SELECT *
FROM Input
WHERE CustID NOT IN (SELECT CustID FROM Production)

无论如何，回到第一个查询，因为这是我们现在必须使用的内容.不幸的是，无法在Linq中的那些特定条件上创建联接，因此我们需要将SQL查询重写为稍差一些的内容(因为我们现在必须从Input读取两次):

Anyway, going back to the first query, since that's what we have to work with for now. Unfortunately it's impossible to create a join on those specific conditions in Linq, so we need to rewrite the SQL query as something slightly worse (because we now have to read from Input twice):

SELECT *
FROM Input
WHERE <Primary Key> NOT IN
(
    SELECT i.<Primary Key>
    FROM Input i
    INNER JOIN Production p
    ON (p.CustID = i.CustID OR (p.CustID IS NULL AND i.CustID IS NULL))
    AND (p.CustName = i.CustName OR (p.CustName IS NULL AND i.CustName IS NULL))
    AND (p.Household = i.Household OR
        (p.Household IS NULL AND i.Household IS NULL))
)

现在，我们有了一些可以最终转换为Linq语法的东西.我们仍然不能明确地进行联接，这是最好的选择，但是我们走了老派，从笛卡尔联接开始，将联接条件放入WHERE段中，服务器仍然可以对它进行排序. :

Now we have something we can finally translate to Linq syntax. We still can't do the join explicitly, which would be best, but we go old-school, start from the cartesian join and toss the join conditions into the WHERE segment, and the server will still be able to sort it out:

var excluded =
    from i in input
    from p in production
    where
        ((p.CustID == i.CustID) || ((p.CustID == null) && (i.CustID == null))) &&
        ((p.CustName == i.CustName) || 
            ((p.CustName == null) && (i.CustName == null))) &&
        ((p.Household == i.Household) ||
            ((p.Household == null) && (i.Household == null)));
    select i.PrimaryKey;

var results =
    from i in input
    where !excluded.Contains(i.PrimaryKey)
    select i;

我在这里假设您桌上有某种主键.如果不这样做，则可能会遇到其他问题，但是可以使用EXCEPT来解决此特定问题:

I'm assuming here that you have some sort of primary key on the table. If you don't, you've got other problems, but you can get around this particular problem using EXCEPT:

var excluded =
    from i in input
    from p in production
    where
        ((p.CustID == i.CustID) || ((p.CustID == null) && (i.CustID == null))) &&
        ((p.CustName == i.CustName) || 
            ((p.CustName == null) && (i.CustName == null))) &&
        ((p.Household == i.Household) ||
            ((p.Household == null) && (i.Household == null)));
    select i;

var results = input.Except(excluded);

这篇关于将此SQL查询转换为Linq(不存在+子查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！