I'm trying to write a query to return rows from a table where the ID is from another table and its not giving me the results I need. I have been trying to use the following query which doesn't work.

exec sp_executesql N'SELECT [WS2].[dbo].[Rim].[ProductVersionID], [WS2].[dbo].[Rim].[RimID], [WS2].[dbo].[Rim].[Depth], [WS2].[dbo].[Rim].[ERD], [WS2].[dbo].[Rim].[FrontOrRear], [WS2].[dbo].[Rim].[IsDisc], [WS2].[dbo].[Rim].[NippleLength], [WS2].[dbo].[Rim].[NumberOfHoles], [WS2].[dbo].[Rim].[TireMountType], [WS2].[dbo].[Rim].[WheelSize], [WS2].[dbo].[Rim].[WheelBuildCost]
FROM [WS2].[dbo].[Rim]
WHERE ( ( [WS2].[dbo].[Rim].[ProductVersionID] IN (SELECT [WS2].[dbo].[SetPart].[ProductVersionID] FROM [WS2].[dbo].[SetPart]
WHERE [WS2].[dbo].[SetPart].[SetID] = @SetID1)))'
, N'@SetID1 int', @SetID1 = 12

The reason I think this isn't working is because there are two rims in the Rim table and the two rims are each added to the SetPart table twice making each rim a duplicate. The query using the IN clause only returns the distinct rims instead of each rim found in the SetPart table. The second query returns what I want, each rim duplicated for each of the rows found in the SetPart table.

I have now figured out that this query does work, but I can't figure out how to make it with the PredicateFactory.

select  
     *
from
     Rim R
INNER JOIN
    (SELECT ProductVersionID FROM SetPart WHERE SetID = 12) SP ON SP.ProductVersionID = R.ProductVersionID

Does anyone know of another way I can solve this?

select * from Rim R INNER JOIN (SELECT ProductVersionID FROM SetPart WHERE SetID = 12) SP ON SP.ProductVersionID = R.ProductVersionID

Could this be represented as

select * from Rim R inner join SetPart SP on SP.ProductVersionID = R.ProductVersionID where SP.SetID = 12

        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            EntityCollection Rims = new EntityCollection(new RimEntityFactory());
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add(RimEntity.Relations.SetPartEntityUsingProductVersionID);
            filter.PredicateExpression.Add(SetPartFields.SetID == 12);
            adapter.FetchEntityCollection(rows, filter);
        }

Ah, this is almost it. On closer inspection I'm seeing that this is going to be more diffecult as there is no defined relation between Rim.ProductVersionID and SetPart.ProductVersionID. The second part of this problem is that Rim is only one of many entities that need to use this. Though the problem of the many entities needing to share this I would solve by using reflection to custom build the relation, I still need the relation to exist. I don't want to have to go through every different entity and create this relation. Is it possible to create a custom relation/join without it actually existing on the entity?

When I was using the IN clause I didn't have to have this join so it was a bit easier to deal with. Can you explain to me why the IN clause won't work for this?

If the relations doesn't exist then you can try this instead.

using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            EntityCollection Rims = new EntityCollection(new RimEntityFactory());
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add(new EntityRelation(RimFields.ProductVersionID, SetPartFields.ProductVersionID, RelationType.OneToMany);
            filter.Relations.Add(RimEntity.Relations.SetPartEntityUsingProductVersionID);
            filter.PredicateExpression.Add(SetPartFields.SetID == 12);
            adapter.FetchEntityCollection(rows, filter);
        }

I don't know what kind of relationship this is between the two, but this will let you define the needed relationship.

bclubb wrote:

If the relations doesn't exist then you can try this instead.

using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            EntityCollection Rims = new EntityCollection(new RimEntityFactory());
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add(new EntityRelation(RimFields.ProductVersionID, SetPartFields.ProductVersionID, RelationType.OneToMany);
            filter.Relations.Add(RimEntity.Relations.SetPartEntityUsingProductVersionID);
            filter.PredicateExpression.Add(SetPartFields.SetID == 12);
            adapter.FetchEntityCollection(rows, filter);
        }

I don't know what kind of relationship this is between the two, but this will let you define the needed relationship.

This is great, thanks. I now just have one more problem with this. The code that I wrote is adding the DISTINCT keyword which is then causing this to not return the results I want. Here is my code.

using(WSDataAccessAdapter adapter = new WSDataAccessAdapter(DBUtil.ConnectionString))
{
    Assembly assembly = Assembly.GetAssembly(partType);
    string productType = partType.Name;

    Type factoryType = assembly.GetType("WrenchScience.LLBLGen.FactoryClasses." + productType + "Factory");
    IEntityFactory2 factory = (IEntityFactory2)factoryType.GetConstructor(new Type[0]).Invoke(new object[0]);
    EntityCollection parts = new EntityCollection(factory);

    RelationPredicateBucket filter = new RelationPredicateBucket();
    EntityField2 field = new EntityField2("ProductVersionID", productType, typeof(Int32), TypeDefaultValue.GetDefaultValue(typeof(Int32)), false, 2, 0, 0, 10, false, false, false);
    filter.Relations.Add(new EntityRelation(field, SetPartFields.ProductVersionID, RelationType.OneToMany));
    filter.PredicateExpression.Add(SetPartFields.SetID == partSetId);

    IPrefetchPath2 prefetchPath = new PrefetchPath2((int)Enum.Parse(typeof(EntityType), productType));
    PropertyInfo prefetchPathProductProperty = partType.GetProperty("PrefetchPathProduct");
    IPrefetchPathElement2 productPath = (IPrefetchPathElement2)partType.GetProperty("PrefetchPathProduct").GetValue(null, new object[0]);
    IPrefetchPathElement2 manufacturerPath = productPath.SubPath.Add(ProductEntity.PrefetchPathManufacturer);
    manufacturerPath.SubPath.Add(ManufacturerEntity.PrefetchPathCompany);
    productPath.SubPath.Add(ProductEntity.PrefetchPathRidingStyle);
    productPath.SubPath.Add(ProductEntity.PrefetchPathProductType);
    prefetchPath.Add(productPath);

    PropertyInfo prefetchPathProductVersionProperty = partType.GetProperty("PrefetchPathProductVersion");
    if (prefetchPathProductVersionProperty != null) 
    {
        IPrefetchPathElement2 prefetchPathProductVersion = (IPrefetchPathElement2)prefetchPathProductVersionProperty.GetValue(null, new object[0]);
        if (prefetchPathProductVersion != null)
            prefetchPath.Add(prefetchPathProductVersion);
    }

    adapter.FetchEntityCollection(parts, filter, prefetchPath);
    return parts;
}

Can you tell me how to stop the distinct keyword from being added? Here is the resulting query.

exec sp_executesql N'SELECT DISTINCT [WS2].[dbo].[Tire].[ProductVersionID], [WS2].[dbo].[Tire].[TireID], [WS2].[dbo].[Tire].[BeadType], [WS2].[dbo].[Tire].[FrontOrRear], [WS2].[dbo].[Tire].[TireMountType], [WS2].[dbo].[Tire].[Width], [WS2].[dbo].[Tire].[WheelSize] FROM ( [WS2].[dbo].[Tire]  INNER JOIN [WS2].[dbo].[SetPart]  ON  [WS2].[dbo].[Tire].[ProductVersionID]=[WS2].[dbo].[SetPart].[ProductVersionID]) WHERE ( ( [WS2].[dbo].[SetPart].[SetID] = @SetID1))', N'@SetID1 int', @SetID1 = 6

Why would you want to have duplicate values? (I haven't read into the details of this thread, so I might ask a dumb question ) The problem is that the entityfetch logic uses distinct to filter out dupes and it also does that when filling the collection, so you'll never get duplicates there, as having a duplicate entity object is not that useful, why have 2 times the same data in different objects... ?

Otis wrote:

Why would you want to have duplicate values? (I haven't read into the details of this thread, so I might ask a dumb question ) The problem is that the entityfetch logic uses distinct to filter out dupes and it also does that when filling the collection, so you'll never get duplicates there, as having a duplicate entity object is not that useful, why have 2 times the same data in different objects... ?

Well, were should I start. Lets say I have a product like Tire, this tire I have one row of in the Product table. I then take this tire and add it to a set twice and then in each row of the set for this tire I mark the first one as being F for front and the second row gets R for rear. I now want to query the tire entity and get a tire for each row in the set with its FrontOrRear value of F or R. This in the end gives me two distinct Tires. There is a lot more going on with this, but this is the basic idea. I thought I could in an IN clause to get my two tire instances, but it also only returns distinct rows. I don't know if this is the best way to do this, but when mulitple entities are returned this works the way I want it to. The way I think about this is that a Tire is not specific to the front or rear, but we still need a way to differenciate between a front and rear.

Though isn't that the result of having two tables merged which should have bene separate? I mean: the Tyre you're talking about is a tyre type, so you reference that from the bike entity for example: fronttireID, backtireID, or am I seeing it wrong?

If you want to add it to a list of entities which make up a bike, you can't add the TYPE twice, you have to add an instance twice (not entity class type, but the type of the tire as data in the db). So not an instance of the tire-type, but an instance of a tyre, which references the tiretype, and which can get the property F or R. Otherwise it would make the TYPE F or R when you set the property to F or R, which isn't the case of course.

Effectively it's this: Bike - Tire is objectified as a new entity. That relation is a new entity: BikeTire, which holds the property Front or Back.

No your thinking is correct but a little different then how I'm doing it. I don't have fixed positions for the parts in a bike. Instead I opted for a more generic table design to hold the parts chosen when building the bike.

Here is a simplified example of the BuildItem table.

BuildItemID, ProductID, ProductTypeID, FrontOrRear

Using the (ProductID, ProductTypeID) I can refetch the correct entity from the entities table. The FrontOrRear value here is used to denote where on the bike the part is to be added when building it. For tire we could have two of the same tire but one is front and the other is rear. We could also have two different tires chosen where one was chosen for the front and the other for the rear.

The problem I'm having is not how to store the parts selected, its how I am trying to fetch the default set of parts from the PartSet/SetPart tables.

The SetPart table is similer to the BuildItem table in its fields which allows me to refetch an entity based on the (ProductID, ProductTypeID, FrontOrRear) combination. When a PartSet is chosen we have to go and fetch each entity in the SetPart table and test it for compatibility with any parts currently in the BuildItem table. This actually worked really well except for the fact that its slow when we have 8 sets and each set has as many as 60 parts in it.

I decided to try and improve the performance of this iteration by fetching the parts from the SetPart table in batches by part type. In some cases there could be as many as 8 of a single product type. Usually these parts all have a distinct ProductID. In come cases there are duplicates of the same ProductID because we have marked one as a front and the other as a rear as is the case for the Tire, Tube and a few others. This works by taking the SetPart.FrontOrRear value and copying it into the BuildItem.FrontOrRear value.

Now the problem is that I can't seem to fetch the entities in a batch by product type because the fetch won't return the duplicates. There are other details in how all this works, but I'm not sure how much I should say since it may not be relevent to the problem. Could this be solved by making the PK of the SetPart table something like (ProductID, ProductTypeID, FrontOrRear)?

Of course fetching the entities from the set in batches by product type is only helping the performance problem a little. I would really like to figure out a way to fetch all the different entities from the collection in a single fetch and then be able to iterate over each different entity to test it compatibility with the current entities in the BuildItem table.

It's a bit hard for me to grasp the whole problem, so forgive me if I babble nonsense, but isn't it better to start from the buildtable and select the data related to that from the setparts table, instead the other way around? Also, correct me if I'm wrong, but IMHO the real problem is the producttypeid: that value signals in which table to look for the particular product, correct? So you can't use simple joins and subqueries to test compatibility of a set, or am I wrong? Either way, you should look for doing set-tests in the db, by trying to formulate them as IN queries and / or joins.

It's often easier to start with what you want to get, how silly that might sound, and then try to formulate the query how to get that data.

I also find it a little odd you're a little uneasy about the PK of SetPart: The unique identifying attributes of SetPart make the PK. IMHO it thus means that producttypeID can be removed from any PK, as that's part of Product (IMHO): it's not possible to have two products, with the same ProductID but different type, or am I wrong?