Return calculated value from a stored procedure, not showing up in my entity.

 OK, I tried looking for similar issues but didnt find what I wanted.

I have a Merchant table that has [Latitude] and [Longitute] (both are double) columns among others. My LS entity has all the same matching properties plus a transient property called "Distance" that is a double.

The Entity is setup with the Table AccessMethod and is normally used this way. We have one case where we are selecting merchants given a users location(lat, long) and a distance. Our sproc returns all the correct fields, plus the relative Distance as a double. I created the sproc reference in LS by dragging the sproc from Server Explorer to the designer and it created an additional entity named after the sproc that matched the result set. Using it this way, the new entities are correctly created and Distance is populated as expected.

The problem is that I dont want to work with a collection of "GetMerchantByDistanceResult", I want to work directly with a collection of "Merchant", so, in the designer, I changed the Entity Type of the sproc to point to Merchant. This sort of works, but it fails to populate the Distance property.

So before I start implementing custom casting or inheritance I figured I'd post the question to see if Im just completely missing something here.

Thanks for your help.

BTW, the sproc is in SQL 2008. The value is not a computed column on the table, its determined in the sproc based on the input location, as compared to the merchants stored location.

Hmm, this is a bit tricky.  You could add Distance to the Merchant type and set its LoadOnly property to true, which would work with the sproc, but then you wouldn't be able to load directly from the table because LightSpeed would want to load a Distance column.  Basically the problem is that LightSpeed assumes that an entity maps to a particular set of columns.  So I don't think there's a way around this in the current build.

However beginning with the next nightly I have implemented a little tweak that will enable you to support this scenario, albeit in a rather odd way.

LightSpeed supports a feature called "named aggregates" which allows you to mark individual fields not to be included when LightSpeed queries the database table, unless the user specifically asks for them.  You can use this feature to put a Distance field on the Merchant entity without LightSpeed trying to select a Distance column.  But now how do you get LightSpeed to load the Distance field when you're using the stored procedure?

Well, beginning in the next nightly, a procedure query will provide the procedure name as an aggregate.  That means that when you load an entity using a stored procedure, fields whose Aggregate Names contains the procedure name will be loaded.  So now you can write something like:

[EagerLoad(AggregateName = "GetMerchantByDistance")]
private readonly double _distance;  // must be readonly

public double Distance { get { return _distance; } }

(In the designer, mark Distance as LoadOnly and enter GetMerchantByDistance in the Aggregate Names setting.)

Now when you load a Merchant via the GetMerchantByDistance sproc, the Distance field will get populated.  But when you load a Merchant directly from the table the Distance field will be ignored.

IMPORTANT: The aggregate name must be the name of the stored procedure in the database.  The name you see on the designer is the name of the C#/VB method we generate for calling the sproc, but at runtime LightSpeed only knows the name of the sproc to call, not the name of the wrapper method.  So if your sproc name is e.g. get_merchant_by_distance then this is what you must use as the aggregate name instead of GetMerchantByDistance.

CAVEAT: Obviously Distance will always be 0 when you load from the table.  You'll need to cope with that yourself.  And of course we can't stop you mixing merchants loaded via the sproc with merchants loaded via the table -- or worse still, merchants whose distance is relative to Point A with merchants whose distance is relative to Point B.  But again this is up to you.

The nightly with this support will be available from about 1200 GMT.  Let us know if you run into any problems!

 Awesome, thank you. This will solve my problem all together on two separate projects, and I wont need to worry about distance when its being read using Table Access, since the Distance I want is only relevant to specific user calls, and not the merchant itself.