I have a database that I cannot change the format of.
Custom objects are stored in a table called Custom_Independent that has several fields like text1,text2 and a app_name field that can be used as a distinguisher.
Any additional fields are store in a seperate table User_Fields, there is also a User_field_defs table that contains the definitions of these fields (like name).
What I'd like to do is have a custom linq entity for each of my custom types eg Invoice that then maps through to the custom field. I can easily use custome properties and code in the get set operations to access the relevant fields, however what I'd really like to do is provide some form of linq extension to handle the mapping for queries.
e.g.
var q = from i in invoices where type=='foo' select i;
would get rewritten to the relevant SQL as
select ..,.. from custom_independent where app_name=='Invoice' and exists (select id from custom_fields where id=custom_independent.id and field_id=23421 and value='foo')
How would I do this?