Thanks Mark, this query would produce exactly the same result. However, it
would be just as hard to generate something like that in the context of my
original post. I was thinking subqueries because they seem to be more
straightforward to generate using Object Query pattern. I also suspect
that subqueries would be faster (even though speed is not the highest
priority).
By the way, I would really like to hear from people out there using O/R
mappers - impressions, feedback?
Cheers,
Roman.
On Fri, 8 Oct 2004 08:29:58 -0400, Potter, Mark S. <[EMAIL PROTECTED]>
wrote:
>Is this okay?
>
>select * from contacts
>
>Id ContactName
>----------- --------------------------------------------------
>1 Contact1
>2 Contact2
>3 Contact3
>4 Contact4
>
>select * from contactattributes
>
>ContactId AttributeId
>----------- --------------------------------------------------
>1 Attrib1
>2 Attrib2
>3 Attrib3
>4 Attrib1
>4 Attrib2
>
>select distinct c.*
>from Contacts c inner join ContactAttributes ca on c.[id] = ca.[ContactId]
>where ca.[AttributeId] in ('Attrib1', 'Attrib2')
>order by [ContactName]
>
>Id ContactName
>----------- --------------------------------------------------
>1 Contact1
>2 Contact2
>4 Contact4
>
>
>
>
>-----Original Message-----
>From: Roman Antchik [mailto:[EMAIL PROTECTED]
>Sent: Friday, October 08, 2004 1:21 AM
>To: [EMAIL PROTECTED]
>Subject: Re: [ADVANCED-DOTNET] Abstracting SQL
>
>
>>> Don't forget you can also put complicated and important
>>> tables in memory
>>> on middle tiers this allows you to scale to many users and
>>> makes the DB
>>> performance irrelevant. These middle tiers also allows your
>>> OO design
>>> to capture business logic that requires sorting , joins , groupings ,
>>> exclusions etc and allows you to have much simpler SQL . ( ie just
>>> simple selects and updates). In this case your SQL grammar
>>> is so simple
>>> it will be DB independent.
>
>Thanks Ben. Unfortunately that is not an option:
>
>1. Sheer volume of data.
>
>2. Straightforward O/R mapper is not hard to implement - this is where we
>stand now. It is the more complex stuff, like subqueries that got us
>stuck.
>
>Regardless, there is no support for subqueries in .NET framework
>DataTable / DataSet classes anyway.
>
>For example the only way to execute the query below - without subqueries -
>would be to have two queries and a union.
>
>Extracting all contacts that either have Attribute 1 or Attribute 2
>(assuming there are three tables - Contacts, Attributes &
>ContactAttributes):
>
>SELECT * FROM Contacts
>WHERE EXISTS
>(
> SELECT * FROM ContactAttributes
> WHERE
> Contact.Id = ContactAttributes.ContactId
> AND
> ContactAttributes.AttributeId = <Some Value>
>)
>OR EXISTS
>(
> SELECT * FROM ContactAttributes
> WHERE
> Contact.Id = ContactAttributes.ContactId
> AND
> ContactAttributes.AttributeId = <Some Other Value>
>)
>
>
>Thanks,
>Roman.
>
>===================================
>This list is hosted by DevelopMentor� http://www.develop.com
>Some .NET courses you may be interested in:
>
>Essential .NET: building applications and components with CSharp
>August 30 - September 3, in Los Angeles
>http://www.develop.com/courses/edotnet
>
>View archives and manage your subscription(s) at
http://discuss.develop.com
>
>===================================
>This list is hosted by DevelopMentor� http://www.develop.com
>Some .NET courses you may be interested in:
>
>Essential .NET: building applications and components with CSharp
>August 30 - September 3, in Los Angeles
>http://www.develop.com/courses/edotnet
>
>View archives and manage your subscription(s) at
http://discuss.develop.com
===================================
This list is hosted by DevelopMentor� http://www.develop.com
Some .NET courses you may be interested in:
Essential .NET: building applications and components with CSharp
August 30 - September 3, in Los Angeles
http://www.develop.com/courses/edotnet
View archives and manage your subscription(s) at http://discuss.develop.com
