Can't run linq to sql query?

  • Thread starter Thread starter Andy B.
  • Start date Start date
A

Andy B.

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?
 
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
 
But, will this fix the original problem?
Scott M. said:
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
Click to expand...
 
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
 
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
 
Back
Top