|
Replies:
7
-
Pages:
1
-
Last Post:
Jan 9, 2008 3:17 AM
by: fabrice.marguerie
|
Threads:
[
Previous
|
Next
]
|
|
Posts:
3
From:
South Africa
Registered:
1/8/08
|
|
|
|
LINQ sub query
Posted:
Jan 8, 2008 3:16 PM
|
|
Hi.
I am looking for the LINQ to do a simle sub-query and I am ready to cry!!! The sql for this is quite simple:
The main part of the query is : select * from tblProperties
I have an image table attached to this(one to many) where tblProperties.imageID = tblImages.imageID
and all i want is all propery rows with one random image per property.
The SQL that I used looks something like:
select propertyName, (select top 1 imageLocation from tblImages order by newID()) as randomImage
from tblProperties
what would be the LINQ for this??
Thank you
Sarel
|
|
Posts:
123
From:
Atlanta, GA
Registered:
11/9/07
|
|
|
|
Re: LINQ sub query
Posted:
Jan 8, 2008 3:41 PM
in response to:
Sarel
|
|
I'm not sure that LINQ to SQL supports the CLR based Random or TSQL based newID natively. I can see a couple options:
1) Use a pass-through query to get the results
2) Use a view to return the ID of the record from tblImages from your subselect and map to that view. Then use a LINQ query to join the tblProperties through the Randomize view and then to the tblImages
3) Use a scalar TSQL Function to get a random picture and include that in your LINQ query.
4) Use a stored procedure to return the results.
We discuss consuming pass-through queries, functions and stored procedures in chapter 8. Read through that section and see if it helps.
|
|
Posts:
209
From:
France
Registered:
4/28/06
|
|
|
|
Re: LINQ sub query
Posted:
Jan 8, 2008 4:00 PM
in response to:
jwooley
|
|
To add to Jim's reply, here is how to create a function that returns a random GUID:
CREATE VIEW RandomView
AS
SELECT NEWID() As ID
GO
CREATE FUNCTION GetNewId
(
)
RETURNS uniqueidentifier
AS
BEGIN
RETURN (SELECT ID FROM RandomView)
END
You can then map the GetNewId user defined function using LINQ to SQL's Function attribute. As indicated by Jim, see chapter 8 for the details.
|
|
Posts:
123
From:
Atlanta, GA
Registered:
11/9/07
|
|
|
|
Re: LINQ sub query
Posted:
Jan 8, 2008 4:11 PM
in response to:
fabrice.marguerie
|
|
Actually, Fabrice, in your example you are just pulling a random GUID regardless of whether it appears in the image table or not. In this example, you would want the function to return the imagelocation rather than the ID. Thus the function would be something along the lines of:
CREATE FUNCTION GetRandomImage
()
RETURNS nvarchar(255)
AS
BEGIN
RETURN (select top 1 imageLocation from tblImages order by newID())
END
|
|
Posts:
209
From:
France
Registered:
4/28/06
|
|
|
|
Re: LINQ sub query
Posted:
Jan 8, 2008 4:18 PM
in response to:
jwooley
|
|
You're right, but that was on purpose. Mapping a generic function like the one I suggest allows us to use it for more queries than just the ones where you need a random image. It could be used each time randomization is required in a LINQ to SQL query.
|
|
Posts:
3
From:
South Africa
Registered:
1/8/08
|
|
|
|
Re: LINQ sub query
Posted:
Jan 9, 2008 12:32 AM
in response to:
jwooley
|
|
Thanks for that reply!! Ok, let's say that I create the function to return a random image based on the propertyID and I call it GetRandomImage(@propertyID) what would the LINQ be to get the property and the random image??
Is this book only available in paper format?? Can I download a pdf somewhere?? I am in South-Africa and by the time the book gets here I do not need it any more.
Thanks once again for the reply, do you also know of a good forum to get LINQ answers??
Sarel
|
|
Posts:
3
From:
South Africa
Registered:
1/8/08
|
|
|
|
Re: LINQ sub query
Posted:
Jan 9, 2008 12:54 AM
in response to:
jwooley
|
|
Thanks, chapter 8....... how is the fastest way for me to get hold of this??
Thanks you
Sarel
|
|
Posts:
209
From:
France
Registered:
4/28/06
|
|
|
|
Re: LINQ sub query
Posted:
Jan 9, 2008 3:17 AM
in response to:
Sarel
|
|
In fact, the book won't be available in paper before the end of this month. But it has been available in PDF for several months already. See http://manning.com/marguerie
|
|
|
Legend
|
|
Gold: 300
+
pts
|
|
Silver: 100
- 299
pts
|
|
Bronze: 25
- 99
pts
|
|
Manning Author
|
|
Manning Staff
|
|
