Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Can't run linq to sql query?

Reply
Thread Tools

Can't run linq to sql query?

 
 
Andy B.
Guest
Posts: n/a
 
      09-08-2009
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?


 
Reply With Quote
 
 
 
 
Scott M.
Guest
Posts: n/a
 
      09-08-2009
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." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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?
>



 
Reply With Quote
 
 
 
 
Andy B.
Guest
Posts: n/a
 
      09-08-2009
But, will this fix the original problem?
"Scott M." <(E-Mail Removed)> wrote in message
news:eu%(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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?
>>

>
>



 
Reply With Quote
 
Scott M.
Guest
Posts: n/a
 
      09-08-2009
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." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> But, will this fix the original problem?
> "Scott M." <(E-Mail Removed)> wrote in message
> news:eu%(E-Mail Removed)...
>> 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." <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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?
>>>

>>
>>

>
>



 
Reply With Quote
 
Andy B.
Guest
Posts: n/a
 
      09-08-2009
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 (VS200 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." <(E-Mail Removed)> wrote in message
news:eV$(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> But, will this fix the original problem?
>> "Scott M." <(E-Mail Removed)> wrote in message
>> news:eu%(E-Mail Removed)...
>>> 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." <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>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?
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linq or not Linq George ASP .Net 4 11-05-2008 04:53 PM
LINQ to SQL and new MS SQL 2008 datatypes Mike Gleason jr Couturier ASP .Net 1 10-29-2008 03:42 AM
Linq and sql Ilyas ASP .Net 1 03-13-2008 08:25 PM
SQL Query to Linq (Group By and Sum) Alex Sauceda ASP .Net 6 02-04-2008 10:26 PM
Linq to SQL custom paging Mike ASP .Net 0 11-15-2007 04:33 PM



Advertisments