帮助“排除"操作员 [英] Help with "Except" operator

问题描述

我在以下代码中使用"Except"运算符.它可以很好地返回差异，但是我得到的只是PK_PhoneTypeID(当然).我需要用"PhoneTypes"填充一个下拉列表.我无法弄清楚如何使用除"查询返回此列，或者如何嵌套另一个查询以返回"PhoneTypes"列.

这基本上是表格的样子:

电话
PK_ID
FK_ContactID
FK_PhoneTypeID

PhoneTypes
PK_PhoneTypeID
电话类型

这是我的"Except"代码和ddl绑定:

I am using the "Except" operator in the following code. It returns the differences fine, but all I''m getting is the PK_PhoneTypeID (of course). I need to populate a Drop-Down List with "PhoneTypes". I cant figure out how to return this column with my "Except" query, or how to nest another query to return the "PhoneTypes" column.

This is basically what the tables look like:

Phones
PK_ID
FK_ContactID
FK_PhoneTypeID

PhoneTypes
PK_PhoneTypeID
PhoneTypes

Here is my "Except" code and ddl binding:

Dim hiddenGUID As HiddenField = CType(Wizard1.FindControl("Wizardstep3$HiddenField1"), HiddenField)

        Dim dc As New DataClassesDataContext()

        Dim q = (From x In dc.PhoneTypes _
                Select x.PhoneTypeID).Except _
                    (From c In dc.Phones _
                    Where c.ContactID.ToString() = hiddenGUID.Value _
                    Select c.PhoneTypeID)


        'The two commented lines below is what I'm trying to do

        DropDownList2.DataSource = q
        'DropDownList2.DataTextField = "PhoneTypes"
        'DropDownList2.DataValueField = "PhoneTypeID"
        DropDownList2.DataBind()

推荐答案

HiddenField1")，HiddenField) Dim dc As 新建 DataClassesDataContext() 暗 q =(来自x In dc.PhoneTypes _ 选择 x.PhoneTypeID). (来自c 位于 dc中.电话_ 其中c.ContactID.ToString()= hiddenGUID.Value _ 选择 c.PhoneTypeID) ' 下面两行注释是我要尝试的操作 DropDownList2.DataSource = q ' DropDownList2.DataTextField ="PhoneTypes" ' DropDownList2.DataValueField ="PhoneTypeID" DropDownList2.DataBind()

HiddenField1"), HiddenField) Dim dc As New DataClassesDataContext() Dim q = (From x In dc.PhoneTypes _ Select x.PhoneTypeID).Except _ (From c In dc.Phones _ Where c.ContactID.ToString() = hiddenGUID.Value _ Select c.PhoneTypeID) 'The two commented lines below is what I'm trying to do DropDownList2.DataSource = q 'DropDownList2.DataTextField = "PhoneTypes" 'DropDownList2.DataValueField = "PhoneTypeID" DropDownList2.DataBind()

编写一个sql语句来做到这一点很容易，所以我决定直接在Linq to SQL中使用该语句.

Writing a sql statement to do this was easy, so I decided to use the statement directly in Linq to SQL

Dim q As IEnumerable(Of PhoneType) = _
        dc.ExecuteQuery(Of PhoneType) _
                ("select pt.PhoneTypeID, pt.PhoneTypes " & _
                "from PhoneTypes as pt " & _
                "where Not exists " & _
                "(select PhoneTypeID " & _
                "from phones " & _
                "where PhoneTypeID = pt.PhoneTypeID and ContactID ='" + hiddenGUID.Value + "') " & _
                "order by pt.PhoneTypeID;")
        DropDownList2.DataSource = q
        DropDownList2.DataTextField = "PhoneTypes"
        DropDownList2.DataValueField = "PhoneTypeID"
        DropDownList2.DataBind()

我一直在玩耍，发现了一个纯Linq to SQL解决方案:

I Kept playing around and found a pure Linq to SQL solution:

Dim q = From pt In dc.PhoneTypes _
 Where Not (Not (From phones In dc.Phones _
 Where phones.PhoneTypeID = pt.PhoneTypeID And phones.ContactID = ContactGUID _
 Select New With {phones.PhoneTypeID}).Single() Is Nothing) _
 Select pt.PhoneTypeID, pt.PhoneTypes

我希望这对某人有帮助...

I hope this helps someone...

这篇关于帮助“排除"操作员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！