SubSonic primary key issue

At our organization we have to use the name ID_NO for the primary keys in our tables, a little strange to me but I can't change that however it is causing subsonic to complain and I am hoping someone with more experience can help me out.

Here is the error I get

Can't decide which property to consider the Key - you can create one called 'ID' or mark one with SubSonicPrimaryKey attribute [System.InvalidOperationException]

Here is my query(s) there is actually two.

var excludedVendors = (from vndMapping in db.COMPANIES_VND_MAPPINGS
                         join compView in db.COMPANIES_COMPANIES_VS on vndMapping.VENDOR_ID equals compView.COMPANY_ID
                         join cmpTier in db.CT_CT_CMP_TIERS on compView.TIER_CODE equals cmpTier.CODE
                         where (vndMapping.OEM_ID == null || vndMapping.OEM_ID == oemId)
                         && (vndMapping.MODEL_ID == null || vndMapping.MODEL_ID == modelId)
                         && (vndMapping.MODALITY_ID == null || vndMapping.MODALITY_ID == modalityId)
                         && (vndMapping.CLASS_ID == null || vndMapping.CLASS_ID == productTypeId)
                         && vndMapping.EXCLUDE == null
                         select
                           vndMapping.VENDOR_ID
                                  );

  possibleVendors = (from vndMapping in db.COMPANIES_VND_MAPPINGS
                     join compView in db.COMPANIES_COMPANIES_VS on vndMapping.VENDOR_ID equals compView.COMPANY_ID
                     join cmpTier in db.CT_CT_CMP_TIERS on compView.TIER_CODE equals cmpTier.CODE
                     where (vndMapping.OEM_ID == null || vndMapping.OEM_ID == oemId)
                     && (vndMapping.MODEL_ID == null || vndMapping.MODEL_ID == modelId)
                     && (vndMapping.MODALITY_ID == null || vndMapping.MODALITY_ID == modalityId)
                     && (vndMapping.CLASS_ID == null || vndMapping.CLASS_ID == productTypeId)
                     && !excludedVendors.Any(x => x == vndMapping.VENDOR_ID)
                     && (compView.COMPANY_TYPE_ID == 2 || compView.COMPANY_TYPE_ID == 3)
           select new VendorProxy
           {
             DiscountPercent = (double)compView.DISCOUNT_PERCENT,
             OrderNo = cmpTier.ORDER_NO,
             PhoneNumber = "",
             TierCode = cmpTier.CODE,
  开发者_开发问答           TierDescription = cmpTier.DESCRIPTION,
             VendorId = vndMapping.VENDOR_ID,
             VendorName = compView.COMPANY_NAME
           }).OrderBy(x => x.OrderNo).ThenBy(x => x.VendorName).ToList<VendorProxy>();

it wasn't until I added && !excludedVendors.Any(x => x == vndMapping.VENDOR_ID) that I got the problem, I can't seem to figure out how to fix it though, i thought I would go to subsonic's site and find the answer in two seconds of course that isn't happening.

Thanks for any input...

I am using version 3.0.0.3, I just found this in the structs file which we are generating via a t4

  public class COMPANIES_VND_MAPPINGTable: DatabaseTable {

        public COMPANIES_VND_MAPPINGTable(IDataProvider provider):base("VND_MAPPINGS",provider){
            ClassName = "COMPANIES_VND_MAPPING";
            SchemaName = "COMPANIES";


            Columns.Add(new DatabaseColumn("ID_NO", this)
            {
                IsPrimaryKey = true,
                DataType = DbType.Decimal,
                IsNullable = false,
                AutoIncrement = false,
                IsForeignKey = false,
                MaxLength = 15
                //CleanName = "ID_NO" Temporarily removed. The 'CleanName' property does not exist on this class in the SubSonic3 master repo on GitHub. Did someone miss a file checkin?
            });

            Columns.Add(new DatabaseColumn("VENDOR_ID", this)
            {
                IsPrimaryKey = false,
                DataType = DbType.Decimal,
                IsNullable = false,
                AutoIncrement = false,
                IsForeignKey = false,
                MaxLength = 15
                //CleanName = "VENDOR_ID" Temporarily removed. The 'CleanName' property does not exist on this class in the SubSonic3 master repo on GitHub. Did someone miss a file checkin?
            });

Notice the IsPrimaryKey = true - Hmm?

One problem is that you have to apply the SubSonicPrimaryKey attribute to the property for the primary key in your model object, which is generated code.

So you can't just try and put it there manually like Drackir is showing. Next time your templates get generated that change will get blown away, and you can't apply the attribute in a partial class because the field is already defined.

You will have to change the templates (also, perhaps you should update to the most current version of everything, the commented out code from your structs.cs file shows you have some things mismatched and out of date).

There are several ways you could change the template to make this work. The easiest might be to tell the template to ignore ID_NO fields completely and then manually define them all in partial classes. Another option would be to make the template add the attribute to the appropriate field when it generates them.

Really, though, every time I answer a SubSonic question - and moreso as time goes on - I keep thinking, "I should tell them to get the heck away from SubSonic while they can." It's got plenty of pitfalls like this and it really isn't being developed to any substantial degree any longer. I'll probably get some hate for saying that, but I know we decided months ago to get as far away from it as possible on all projects.

From what I gather SubSonic helps link up your classes (perhaps generates them?) and your database. According to the Convention docs:

Primary Keys

If you want to use SubSonic to access your table, you need to have a Primary Key defined for your table. This is good practice in every case and we need it to do certain things with your table. If you don't have a Primary Key defined, your class won't be generated.

So, assuming you've defined a Primary Key on your table structure and it's still not working, we need to find something else to tell it what to look for. The page also goes on to say this:

Primary Keys

If you call a column “ID” or “Key” or “[ClassName]ID” – no matter it’s type – that will be your Primary Key. If you have other things in mind you can use a primary key attribute [SubSonicPrimaryKey] contained in the SubSonic.SqlGeneration.Schema namespace and we’ll use that column.

The key here is that you have to explicitly tell it what column (class property/field) to use as the primary key by adding the [SubSonicPrimaryKey] attribute. To do this, go into your class file (I guess for whatever type vndMapping is) and add [SubSonicPrimaryKey] above the VENDOR_ID property's declaration.

So for example, something like this:

class VendorMap {
    [SubSonicPrimaryKey]
    public int VENDOR_ID { get; set; }
}

Please note I don't use SubSonic but from what their site says, this seems to make sense.

Edit
Based on your edit, try replacing Columns.Add(new DatabaseColumn("ID_NO", this) { /*...*/ }); with something like this:

[SubSonicPrimaryKey]
DatabaseColumn idcol = new DatabaseColumn("ID_NO", this)
{
    IsPrimaryKey = true,
    DataType = DbType.Decimal,
    IsNullable = false,
    AutoIncrement = false,
    IsForeignKey = false,
    MaxLength = 15
    //CleanName = "ID_NO" Temporarily removed. The 'CleanName' property does not exist on this class in the SubSonic3 master repo on GitHub. Did someone miss a file checkin?
};
Columns.Add(idcol);