This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I know I'll have a hard time explaining this, but here goes: I have an application where entity fields aren't known at design time, so I made a "CustomField" entity, which contains metadata such as field name, type, etc... and a CustomProperty which contains the value and a reference to the CustomField... this information is stored in SQL per row, instead of per column in a traditionally normalized table... The architecture allows my customers to add fields to entities at runtime. For example, if I provide a "Person" entity, and a customer of mine wants to add "First Name" "Last Name" "Age" and "IQ" fields, they can. The underlying system creates a CustomField for each of those fields and associated it with the "Person" entity. I've seen an acronym for this type of vertical vs. horizontal design before, but I can't seem to find it. My issue as with most designers who utilize this pattern, pertains to the parameterized search of those custom fields. Following the example above, The parameterized search query might be "find Persons whose Age = 21" I've run into a brick wall as to how I would implement this query in LightSpeed... While I can find Persons with who have the "Age" CustomField, and I can find persons who have a CustomProperty with a value of "21"; I can't find Person's where the value of "21" is bound to the field of "Age"... Person.CustomField.Name=="Age" && Person.CustomProperty.Value==21 But this would find both of the following people: Person with Age = 21 and IQ = 150 and Person with Age = 3 and IQ = 21 I'm doing a horrible job of explaining, but I figure you expert developers out there would know what I'm talking about and could recommend a way to handle such dynamic use of LightSpeed... |
|
|
I think I understand your scenario, but let me echo it back to you to be sure. If I've misunderstood your setup, please let me know. |
|
|
I think you do understand the scenario. Search by property then convert those results to Person... Since it's a parameterized search, what if I wanted to search more properties to find people? For instance, in my example, what if I wanted people "Age" 21 and "IQ" > 100? One more caveat, I won't know the actual query at design time (so LINQ is probably out). Perhaps I could iteratively find the properties, but then how would I convert the results to people (unless I brute force it with for loops etc) i.e.
var props = uow.Find<CustomProperty>( && var props2 = uow.Find<CustomProperty>( I guess I can make methods to allow me to call each of those queries at runtime, but then I'd need an algorithm to merge Person from props and props2 (from each call basically, until my query is complete or I have no more matching Person). Finally, as you said, my Person entity has some standard attributes... the final query could be based on those as well (a mix of these custom properties and standard attributes) Let me know if such a question is beyond the scope of lightspeed and I'll stop bothering you! Thanks!
|
|
|
If you don't mind merging the result sets on the client, you don't need to write an algorithm to merge them: you can just use the .NET 3.5 sequence operators. var aged21props = uow.Find<CustomProperty>(EA("CustomField.Name") == "Age" && EA("Value") == 21); var brainy21yearoldprops = aged21props.Where(ap => brainyProps.Any(bp => bp.PersonId == ap.PersonId)); // could also use Intersect with a custom IEqualityComparer var brainy21yearolds = brainy21yearoldprops.Select(p => p.Person); Obviously the impact of this is that if there are lots of 21-year-olds and lots of brainy people, but very few brainy 21-year-olds, it will be pretty inefficient. I'm trying to figure out a way of doing a single query that will pick out the combined set and will get back to you if I find something, but this may be beyond what LightSpeed can do. Using standard attributes into a query will be no problem: just traverse to the associated Person the same way as you traverse to the associated CustomField, e.g.: var aged21CalledBob = uow.Find<CustomProperty>( |
|
|
We've had a bit of a brainstorm about this and we're reasonably certain this can't be done solely in LightSpeed. However one thing that might be a possibility is to write a stored procedure that takes a set of key-operation-value triples, and constructs and executes dynamic SQL derived from those query specifiers. You could then call this stored procedure from LightSpeed so that you get the results back as Person objects and get all the benefits of working with LightSpeed entities rather than raw data objects. (Specifically, the Person entities will still be mapped to their underlying tables, even though you retrieved them using a stored proc, so you don't need special insert or update sprocs to save them: LightSpeed will just save them direct to the tables.) |
|
|
How do I send a "set of triples" to a stored procedure (in general, and through LightSpeed). Forgive my ignorance, but I am not aware of a way to send a list or array of values to a stored procedure. Additionally, in the LightSpeed documentation, ProcedureParameter simply takes named Objects as input, and I wouldn't know what .net Object to send? Thanks so much, ~Bill |
|
|
Open-ended collections will be database dependent, but if the complexity of your queries is bounded (e.g. the user will never be able to specify more than 3 criteria) then you can do it by just repeating the parameters or creating different sprocs for each number of criteria. For example you might have: CREATE PROCEDURE ExtQuery( (I acknowledge the ugliness.) From LightSpeed you can then call this using: Find<T>(new ProcedureQuery("ExtQuery", Clearly this could be made a lot nicer by having a wrapper function which built up the procedure parameters array rather than having to spell out all the items by hand, so we could write e.g. Find<T>(ExtQuery.Create( private ProcedureQuery Create(params ExtQueryArg[] args) { ... } If you need an open-ended solution then depending on your database you may need to investigate array support or you may need to kludge it. For example SQL Server does not have array support so you would need to use some sort of workaround: there's extensive discussion at http://www.sommarskog.se/arrays-in-sql-2005.html. (Though this could turn out to be a blessing in disguise because if you wanted more complex and/or nesting of query clauses then arrays wouldn't suffice anyway.) Oracle does have array support (I think... the documentation is not great) though I think it's usable only from ODP.NET and I haven't investigated how this would work with LightSpeed. Again, I appreciate that none of these solutions are very pretty, but what you're doing isn't something that LightSpeed will easily cope with -- sorry! |
|
|
Please don't be sorry, you've been a huge help! I need an open ended solution, so I'll choose one of the pack/unpack methods from that link. Thanks so much, I know my questioning quickly expanded outside the scope of LightSpeed and into unrelated territory. Thanks again, |
|