Can't run linq to sql query?

Discussion in 'ASP .Net' started by Andy B., Sep 8, 2009.

  1. Andy B.

    Andy B. Guest

    I have the following method that I created to extend the DataContext.

    Imports System.Linq


    Namespace EternityRecords.Modules.Testimonials
    Partial Public Class TestimonialsDataContext

    Public Function GetRandomTestimonial(ByVal ModuleID As Integer) As
    Testimonial
    If ModuleID = Nothing Then
    Throw New ArgumentException("ModuleID must not be null or nothing")
    End If
    Dim RandomTestimonial As New Testimonial()

    Dim Query = _
    From T In Me.Testimonials _
    Where T.ModuleID = ModuleID _
    Order By Guid.NewGuid() _
    Take 1

    RandomTestimonial = Query(0)
    Return RandomTestimonial
    End Function
    End Class
    End Namespace

    I get this error:
    An item with the same key has already been added.

    Because I use DNN 5.1.2, extra exception dtails aren't available. Any idea
    why this error happens, and how do you fix it?
     
    Andy B., Sep 8, 2009
    #1
    1. Advertising

  2. Andy B.

    Scott M. Guest

    Just a quick FYI on what may be unrelated to the main issue, but you are
    checking to see if the ModuleID Integer parameter equals Nothing as a
    validation on the input. This check is not going to do anything for you
    since the parameter is not marked as optional. That means that anyone
    calling your method MUST pass an Integer or they will get a compiler error
    for not calling the method with the correct parameters.

    Secondly, even if the parameter was marked as optional, an Integer cannot be
    Nothing anyway. All value types MUST be initialized and if it is not done
    explicitly, then the compiler will initialize Integer types at zero.
    Although your test for Nothing compiles, it turnes out that the compiler
    with actually be checking for the Integer zero, rather than Nothing.

    Also, when you do test for Nothing, it is done with eference types (classes)
    to see if a variable is pointing to an instance. When used this way (the
    correct way), you don't check for equality (=). You use the "Is" or "IsNot"
    operators. So the line would look like:

    If var Is Nothing then...

    Because you are using a value type (Integer) and the equality operator (=)
    the compiler treats that as a test for the default value of an Integer (0).

    If you want to have an Integer than can, in fact, have a Nothing (Null)
    value, you should be using the Nullable Of(Integer) type, rather than the
    Integer type.

    -Scott


    "Andy B." <> wrote in message
    news:...
    >I have the following method that I created to extend the DataContext.
    >
    > Imports System.Linq
    >
    >
    > Namespace EternityRecords.Modules.Testimonials
    > Partial Public Class TestimonialsDataContext
    >
    > Public Function GetRandomTestimonial(ByVal ModuleID As Integer) As
    > Testimonial
    > If ModuleID = Nothing Then
    > Throw New ArgumentException("ModuleID must not be null or nothing")
    > End If
    > Dim RandomTestimonial As New Testimonial()
    >
    > Dim Query = _
    > From T In Me.Testimonials _
    > Where T.ModuleID = ModuleID _
    > Order By Guid.NewGuid() _
    > Take 1
    >
    > RandomTestimonial = Query(0)
    > Return RandomTestimonial
    > End Function
    > End Class
    > End Namespace
    >
    > I get this error:
    > An item with the same key has already been added.
    >
    > Because I use DNN 5.1.2, extra exception dtails aren't available. Any idea
    > why this error happens, and how do you fix it?
    >
     
    Scott M., Sep 8, 2009
    #2
    1. Advertising

  3. Andy B.

    Andy B. Guest

    But, will this fix the original problem?
    "Scott M." <> wrote in message
    news:eu%...
    > Just a quick FYI on what may be unrelated to the main issue, but you are
    > checking to see if the ModuleID Integer parameter equals Nothing as a
    > validation on the input. This check is not going to do anything for you
    > since the parameter is not marked as optional. That means that anyone
    > calling your method MUST pass an Integer or they will get a compiler error
    > for not calling the method with the correct parameters.
    >
    > Secondly, even if the parameter was marked as optional, an Integer cannot
    > be Nothing anyway. All value types MUST be initialized and if it is not
    > done explicitly, then the compiler will initialize Integer types at zero.
    > Although your test for Nothing compiles, it turnes out that the compiler
    > with actually be checking for the Integer zero, rather than Nothing.
    >
    > Also, when you do test for Nothing, it is done with eference types
    > (classes) to see if a variable is pointing to an instance. When used this
    > way (the correct way), you don't check for equality (=). You use the "Is"
    > or "IsNot" operators. So the line would look like:
    >
    > If var Is Nothing then...
    >
    > Because you are using a value type (Integer) and the equality operator (=)
    > the compiler treats that as a test for the default value of an Integer
    > (0).
    >
    > If you want to have an Integer than can, in fact, have a Nothing (Null)
    > value, you should be using the Nullable Of(Integer) type, rather than the
    > Integer type.
    >
    > -Scott
    >
    >
    > "Andy B." <> wrote in message
    > news:...
    >>I have the following method that I created to extend the DataContext.
    >>
    >> Imports System.Linq
    >>
    >>
    >> Namespace EternityRecords.Modules.Testimonials
    >> Partial Public Class TestimonialsDataContext
    >>
    >> Public Function GetRandomTestimonial(ByVal ModuleID As Integer) As
    >> Testimonial
    >> If ModuleID = Nothing Then
    >> Throw New ArgumentException("ModuleID must not be null or nothing")
    >> End If
    >> Dim RandomTestimonial As New Testimonial()
    >>
    >> Dim Query = _
    >> From T In Me.Testimonials _
    >> Where T.ModuleID = ModuleID _
    >> Order By Guid.NewGuid() _
    >> Take 1
    >>
    >> RandomTestimonial = Query(0)
    >> Return RandomTestimonial
    >> End Function
    >> End Class
    >> End Namespace
    >>
    >> I get this error:
    >> An item with the same key has already been added.
    >>
    >> Because I use DNN 5.1.2, extra exception dtails aren't available. Any
    >> idea why this error happens, and how do you fix it?
    >>

    >
    >
     
    Andy B., Sep 8, 2009
    #3
  4. Andy B.

    Scott M. Guest

    As I said, I'm not sure it's related to the problem you are having, but I
    wanted to pass along information about the Nothing test.

    I will say that without more information on your underlying data source
    (what TestimonialsDataContext is based on), it's hard to diagnose your LINQ
    to SQL query. For example, you are querying Me.Testimonials, but
    Testimonials is also the name of your namespace. Also, you are trying to
    order your query results by a new .NET GUID, but haven't told us anything
    about the GUIDs that are in your datasource. Why would a new .NET GUID
    match an existing GUID in your data? I wouldn't expect this query to ever
    be able to order the results correctly (maybe I'm not following what's
    happening correctly).

    -Scott

    "Andy B." <> wrote in message
    news:...
    > But, will this fix the original problem?
    > "Scott M." <> wrote in message
    > news:eu%...
    >> Just a quick FYI on what may be unrelated to the main issue, but you are
    >> checking to see if the ModuleID Integer parameter equals Nothing as a
    >> validation on the input. This check is not going to do anything for you
    >> since the parameter is not marked as optional. That means that anyone
    >> calling your method MUST pass an Integer or they will get a compiler
    >> error for not calling the method with the correct parameters.
    >>
    >> Secondly, even if the parameter was marked as optional, an Integer cannot
    >> be Nothing anyway. All value types MUST be initialized and if it is not
    >> done explicitly, then the compiler will initialize Integer types at zero.
    >> Although your test for Nothing compiles, it turnes out that the compiler
    >> with actually be checking for the Integer zero, rather than Nothing.
    >>
    >> Also, when you do test for Nothing, it is done with eference types
    >> (classes) to see if a variable is pointing to an instance. When used
    >> this way (the correct way), you don't check for equality (=). You use
    >> the "Is" or "IsNot" operators. So the line would look like:
    >>
    >> If var Is Nothing then...
    >>
    >> Because you are using a value type (Integer) and the equality operator
    >> (=) the compiler treats that as a test for the default value of an
    >> Integer (0).
    >>
    >> If you want to have an Integer than can, in fact, have a Nothing (Null)
    >> value, you should be using the Nullable Of(Integer) type, rather than the
    >> Integer type.
    >>
    >> -Scott
    >>
    >>
    >> "Andy B." <> wrote in message
    >> news:...
    >>>I have the following method that I created to extend the DataContext.
    >>>
    >>> Imports System.Linq
    >>>
    >>>
    >>> Namespace EternityRecords.Modules.Testimonials
    >>> Partial Public Class TestimonialsDataContext
    >>>
    >>> Public Function GetRandomTestimonial(ByVal ModuleID As Integer) As
    >>> Testimonial
    >>> If ModuleID = Nothing Then
    >>> Throw New ArgumentException("ModuleID must not be null or nothing")
    >>> End If
    >>> Dim RandomTestimonial As New Testimonial()
    >>>
    >>> Dim Query = _
    >>> From T In Me.Testimonials _
    >>> Where T.ModuleID = ModuleID _
    >>> Order By Guid.NewGuid() _
    >>> Take 1
    >>>
    >>> RandomTestimonial = Query(0)
    >>> Return RandomTestimonial
    >>> End Function
    >>> End Class
    >>> End Namespace
    >>>
    >>> I get this error:
    >>> An item with the same key has already been added.
    >>>
    >>> Because I use DNN 5.1.2, extra exception dtails aren't available. Any
    >>> idea why this error happens, and how do you fix it?
    >>>

    >>
    >>

    >
    >
     
    Scott M., Sep 8, 2009
    #4
  5. Andy B.

    Andy B. Guest

    Hi. Sorry about that. Here are the requirements for the linq query:

    Select all columns in the table taking the first record found and take one
    at random. One of the ways I found online to randomize sql query resultsets
    is to put order by NewID in the sql query with a top(1) clause at the
    beginning.

    Here goes the problem I have to solve, hopefully this helps to figure out
    the problem.

    1. I have the table definition below. This is the Testimonials table to run
    the linq query on.

    /****** Object: Table [dbo].[Testimonials] Script Date: 09/04/2009
    12:08:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
    OBJECT_ID(N'[dbo].[Testimonials]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Testimonials](
    [ModuleID] [int] NOT NULL,
    [ItemID] [int] IDENTITY(1,1) NOT NULL,
    [CreatedByUser] [int] NOT NULL,
    [DateCreated] [date] NOT NULL,
    [Title] [varchar](200) NOT NULL,
    [AuthorName] [varchar](200) NOT NULL,
    [AuthorTitle] [varchar](100) NULL,
    [AuthorPhoneNumber] [varchar](30) NULL,
    [AuthorStreetAddress] [varchar](200) NULL,
    [AuthorCity] [varchar](30) NULL,
    [AuthorState] [char](2) NULL,
    [AuthorZip] [char](5) NULL,
    [Comments] [varchar](5000) NOT NULL,
    CONSTRAINT [PK_Testimonials] PRIMARY KEY CLUSTERED
    (
    [ModuleID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
    OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    SET ANSI_PADDING OFF
    GO
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id =
    OBJECT_ID(N'[dbo].[Testimonials]') AND name = N'IX_Testimonials')
    CREATE NONCLUSTERED INDEX [IX_Testimonials] ON [dbo].[Testimonials]
    (
    [ModuleID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
    OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    /****** Object: Default [DF_Testimonials_DateCreated] Script Date:
    09/04/2009 12:08:45 ******/
    IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id =
    OBJECT_ID(N'[dbo].[DF_Testimonials_DateCreated]') AND parent_object_id =
    OBJECT_ID(N'[dbo].[Testimonials]'))
    Begin
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
    OBJECT_ID(N'[DF_Testimonials_DateCreated]') AND type = 'D')
    BEGIN
    ALTER TABLE [dbo].[Testimonials] ADD CONSTRAINT
    [DF_Testimonials_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
    END


    End
    GO
    /****** Object: Check [CK_Testimonials_AuthorZip] Script Date:
    09/04/2009 12:08:45 ******/
    IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
    OBJECT_ID(N'[dbo].[CK_Testimonials_AuthorZip]') AND parent_object_id =
    OBJECT_ID(N'[dbo].[Testimonials]'))
    ALTER TABLE [dbo].[Testimonials] WITH CHECK ADD CONSTRAINT
    [CK_Testimonials_AuthorZip] CHECK (([AuthorZip] like
    '[0-9][0-9][0-9][0-9][0-9]'))
    GO
    IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
    OBJECT_ID(N'[dbo].[CK_Testimonials_AuthorZip]') AND parent_object_id =
    OBJECT_ID(N'[dbo].[Testimonials]'))
    ALTER TABLE [dbo].[Testimonials] CHECK CONSTRAINT
    [CK_Testimonials_AuthorZip]
    GO
    /****** Object: ForeignKey [FK_Testimonials_Modules] Script Date:
    09/04/2009 12:08:45 ******/
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
    OBJECT_ID(N'[dbo].[FK_Testimonials_Modules]') AND parent_object_id =
    OBJECT_ID(N'[dbo].[Testimonials]'))
    ALTER TABLE [dbo].[Testimonials] WITH NOCHECK ADD CONSTRAINT
    [FK_Testimonials_Modules] FOREIGN KEY([ModuleID])
    REFERENCES [dbo].[Modules] ([ModuleID])
    ON DELETE CASCADE
    NOT FOR REPLICATION
    GO
    IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
    OBJECT_ID(N'[dbo].[FK_Testimonials_Modules]') AND parent_object_id =
    OBJECT_ID(N'[dbo].[Testimonials]'))
    ALTER TABLE [dbo].[Testimonials] CHECK CONSTRAINT [FK_Testimonials_Modules]
    GO

    2. This is the sql statement I need to convert into linq. The statement has
    been tested several times and works as expected.

    select ModuleID,

    ItemID,

    CreatedByUser,

    DateCreated,

    Title,

    AuthorName,

    AuthorTitle,

    AuthorPhoneNumber,

    AuthorStreetAddress,

    AuthorCity,

    AuthorState,

    AuthorZip,

    Comments from Testimonials

    with (nolock)

    left outer join Users on Testimonials.CreatedByUser = Users.UserId from
    Testimonials

    order by NewID()


    3. The TestimonialsDataContext and the Testimonial object are based on the
    Testimonials table listed in #1 above. All I did was drag the Testimonials
    table from server explorer (VS2008) onto the O/R designer.

    4. The GetRandomTestimonial(ByVal ModuleID as Integer) as Testimonial method
    is supposed to represent the sql in #2 above. It takes the DotNetNuke
    ModuleID number as the parameter, generates a random row from the
    Testimonials table from a linq query and returns a Testimonial object (Linq
    table object) as the return value. The complete text of the extended
    TestimonialsDataContext class that I have writen are below.

    Imports System.Linq


    Namespace EternityRecords.Modules.Testimonials

    Partial Public Class TestimonialsDataContext

    Public Function GetRandomTestimonial(ByVal ModuleID As Integer) As
    Testimonial

    Dim RandomTestimonial As New Testimonial()

    Dim Query = _

    From Testimonial In Me.Testimonials _

    Where Testimonial.ModuleID = ModuleID _

    Order By Guid.NewGuid() _

    Take 1

    RandomTestimonial = Query(0)

    Return RandomTestimonial

    End Function

    End Class

    End Namespace


    "Scott M." <> wrote in message
    news:eV$...
    > As I said, I'm not sure it's related to the problem you are having, but I
    > wanted to pass along information about the Nothing test.
    >
    > I will say that without more information on your underlying data source
    > (what TestimonialsDataContext is based on), it's hard to diagnose your
    > LINQ to SQL query. For example, you are querying Me.Testimonials, but
    > Testimonials is also the name of your namespace. Also, you are trying to
    > order your query results by a new .NET GUID, but haven't told us anything
    > about the GUIDs that are in your datasource. Why would a new .NET GUID
    > match an existing GUID in your data? I wouldn't expect this query to ever
    > be able to order the results correctly (maybe I'm not following what's
    > happening correctly).
    >
    > -Scott
    >
    > "Andy B." <> wrote in message
    > news:...
    >> But, will this fix the original problem?
    >> "Scott M." <> wrote in message
    >> news:eu%...
    >>> Just a quick FYI on what may be unrelated to the main issue, but you are
    >>> checking to see if the ModuleID Integer parameter equals Nothing as a
    >>> validation on the input. This check is not going to do anything for you
    >>> since the parameter is not marked as optional. That means that anyone
    >>> calling your method MUST pass an Integer or they will get a compiler
    >>> error for not calling the method with the correct parameters.
    >>>
    >>> Secondly, even if the parameter was marked as optional, an Integer
    >>> cannot be Nothing anyway. All value types MUST be initialized and if it
    >>> is not done explicitly, then the compiler will initialize Integer types
    >>> at zero. Although your test for Nothing compiles, it turnes out that the
    >>> compiler with actually be checking for the Integer zero, rather than
    >>> Nothing.
    >>>
    >>> Also, when you do test for Nothing, it is done with eference types
    >>> (classes) to see if a variable is pointing to an instance. When used
    >>> this way (the correct way), you don't check for equality (=). You use
    >>> the "Is" or "IsNot" operators. So the line would look like:
    >>>
    >>> If var Is Nothing then...
    >>>
    >>> Because you are using a value type (Integer) and the equality operator
    >>> (=) the compiler treats that as a test for the default value of an
    >>> Integer (0).
    >>>
    >>> If you want to have an Integer than can, in fact, have a Nothing (Null)
    >>> value, you should be using the Nullable Of(Integer) type, rather than
    >>> the Integer type.
    >>>
    >>> -Scott
    >>>
    >>>
    >>> "Andy B." <> wrote in message
    >>> news:...
    >>>>I have the following method that I created to extend the DataContext.
    >>>>
    >>>> Imports System.Linq
    >>>>
    >>>>
    >>>> Namespace EternityRecords.Modules.Testimonials
    >>>> Partial Public Class TestimonialsDataContext
    >>>>
    >>>> Public Function GetRandomTestimonial(ByVal ModuleID As Integer) As
    >>>> Testimonial
    >>>> If ModuleID = Nothing Then
    >>>> Throw New ArgumentException("ModuleID must not be null or nothing")
    >>>> End If
    >>>> Dim RandomTestimonial As New Testimonial()
    >>>>
    >>>> Dim Query = _
    >>>> From T In Me.Testimonials _
    >>>> Where T.ModuleID = ModuleID _
    >>>> Order By Guid.NewGuid() _
    >>>> Take 1
    >>>>
    >>>> RandomTestimonial = Query(0)
    >>>> Return RandomTestimonial
    >>>> End Function
    >>>> End Class
    >>>> End Namespace
    >>>>
    >>>> I get this error:
    >>>> An item with the same key has already been added.
    >>>>
    >>>> Because I use DNN 5.1.2, extra exception dtails aren't available. Any
    >>>> idea why this error happens, and how do you fix it?
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Andy B., Sep 8, 2009
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anonymous
    Replies:
    0
    Views:
    1,545
    Anonymous
    Oct 13, 2005
  2. ChrisN
    Replies:
    1
    Views:
    562
    Alexey Smirnov
    Aug 7, 2007
  3. Alex Sauceda

    SQL Query to Linq (Group By and Sum)

    Alex Sauceda, Feb 4, 2008, in forum: ASP .Net
    Replies:
    6
    Views:
    41,219
    Alex Sauceda
    Feb 4, 2008
  4. Mike Gleason jr Couturier

    LINQ to SQL and new MS SQL 2008 datatypes

    Mike Gleason jr Couturier, Oct 28, 2008, in forum: ASP .Net
    Replies:
    1
    Views:
    329
    Cowboy \(Gregory A. Beamer\)
    Oct 29, 2008
  5. George

    Linq or not Linq

    George, Nov 4, 2008, in forum: ASP .Net
    Replies:
    4
    Views:
    384
    Mike Gleason jr Couturier
    Nov 5, 2008
Loading...

Share This Page