Hi,

we use linq to llblgen at our project, we need to add a filter to every query that is executed. in that query we need to add a join statement to another table and filter by a field that exist in that table.

can that be done? what is the best practice for this senario?

thanks, avi.

First question is: why do you need this?

I think the following threads should be helpful to you: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9347 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16715

Hi Walla and thanks for your reply,

i have read both of the threads, the case in the first thread is different because the filter is applied to a field in the same table. the case in the second thread is actually very similar to my case if i understood it correctly.

one of the suggestions there where - manipulate the linq query, but this is not possible as we need a generic solution to be applied to all queries.

the thread is very long and maybe some things were misunderstood, could it be? it cannot be done?

Seems you have missed the last post in the second page

Otis wrote:

I already explained to you that what you want to do is not going to work with Linq as linq queries have aliases everywhere.

So to append stuff to these queries, use LINQ elements, e.g. additional where calls. If you don't want that, don't use linq for these queries and use our own query api system.

i've seen this post and hoped the thread was a big misunderstanding...

i - not like alexdummy am open to suggestions as i havent yet wrote anything, i dont care about removing aliases or anything else, is there a way to do this? or llblgen just dosen't support this behavior?

thanks.

• LLBLGen version?
• TemplateSet (Adapter or SelfServicing)?
• What is the join you want to add? Is that relationship present in all of your entities?
• The Join is for limiting the resulset, filter or sort results?

• LLBLGen version? SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll ProductVersion : 2.6.09.0729 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll ProductVersion : 2.6.09.0903

• TemplateSet (Adapter or SelfServicing)? adapter

• What is the join you want to add? Is that relationship present in all of your entities? i need to recognize a situation were a certaine field exists in one of the enteties that is related to the queried entity and if so, add a join to that entity and filter the results according to that field's value.

• The Join is for limiting the resulset, filter or sort results? filter the results.

thanks again for the help, this issue is critical for us.

I need to recognize a situation were a certaine field exists in one of the enteties that is related to the queried entity and if so, add a join to that entity and filter the results according to that field's value.

Is the related entity known, or you need to look for that field in all related entities, to know which entity to join to?

If the related entity is known, does it have relations to all other entities?

the related entity is not known, i need to recognioze the relation to it from the queried entity.

to be specific:

some query in being executed against LinqMetaData, at that point i know only what is the main entity that is to be projected. i need to check if this main entity has a related entity with the field that i look for. if the main entity has this kind of related entity, i need to join the entities and filter the result according to that field's value.

thanks again.

we'll look into it.

Just as a suggestion - create db views that join on to the related entities and perform the filter - map entities to those views rather than the tables, and then use LINQ to query the view based entities ?

I think the views are indeed a good suggestion. Adding extra filters works at the linq query level, later on it's cumbersome. So either you create a method which 'post' processes the linq query and appends a where, or you use views.

We asked this before but for what situation do you need it? It looks like you want to do authorization, where the related table is a user/security table, am I correct? Have you looked at the authorization system we have in place?

Hi, sorry for the late reply.

we cannot use views becouse we need this check for all queries\entities and cannot create a view per entity.

we need this solution as kind of a row based security, the field we look for represents the organization unit an entity is related to.

we cannot use the authorization machanizem becaouse this will be a huge change and the project is 2 months before production.

how can i change the linq query in a generic way? we need the addition to be applied to each executed query without knowing the type of entity.

can it be done?

mma02 wrote:

Hi, sorry for the late reply. we cannot use views becouse we need this check for all queries\entities and cannot create a view per entity.

And why can't you create a view per entity? It's not that hard. If it's time consuming, so is the rest of the solutions you'll have to pick one from. The thing is: if you add a requirement 2 months before the deadline that everything has to be checked with some table you'll have to realize you'll get problems, because security isn't something you can 'add later', it's either designed from the get-go or not added at all.

we need this solution as kind of a row based security, the field we look for represents the organization unit an entity is related to.

so in other words, you've to verify in table B if a user U can load a row from table A, correct?

So how is this done then, in sql? I mean, if you have 100 tables, and every table needs row security (which sounds like a requirement demanded from some person who thought it would be cool to have without thinking through the requirements), all 100 tables have to have a companion table which contains per row which user can load that row, otherwise how can you otherwise filter who can load which row?

we cannot use the authorization machanizem becaouse this will be a huge change and the project is 2 months before production.

In fact, authorization is very little work. Your authorizers load / obtain the security data up front and you simply inject them through dependency injection and that's it. It's the least amount of work, except for the views of course.

how can i change the linq query in a generic way? we need the addition to be applied to each executed query without knowing the type of entity. can it be done?

No. Well... it might, but as I said, it's cumbersome, because all table/view references in the query have aliases, and you first have to find out which aliases, then you have to join with the right table, and linq uses derived tables a lot so it's not going to be easy to dig into these derived tables to add the joins.

As I said, adding security later isn't a good idea, but if you must, pick the situation which works properly. So I'd go for views, and if that's not possible (and I really can't imagine why) you can go for authorization which plugs into our complete persistence system so you then can also control saves/updates/deletes (and no row-based security system ignores row updates/deletes, so I'd be surprised if you would)

This requirenment is not new, there was an assumption that we can extand LLBLGEN to fit our needs.

creating a view per entity - besides being a huge amount of work, is not a good solution becouse: 1, every new table will have to get a matching view. 2, we use oracle and this db uses views not optimaly - when you run a select query, the view is first created and then your query is run against the view - this is a big performance hit.

I don't understand why we need to discuss our architecture decisions,

i have described a senario we're trying to achieve, please help me get it done.

this senario is not out there for my opinion, it is legitimate to want to add a join + filter to another table when executing a select statement using the tool's object model, don't you think?

Again, thanks a lot for your help.

mma02 wrote:

This requirenment is not new, there was an assumption that we can extand LLBLGEN to fit our needs.

though, if we try to advise you something, and you say it's not a good alternative because there's 2 months till a deadline, that's a bit odd, as you should have taken this into account from the get go. Appending something to a query and assume that will work in all occasions is not matching reality for whatever framework you'll use, as the query you're appending to can have unlimited different forms.

creating a view per entity - besides being a huge amount of work, is not a good solution becouse: 1, every new table will have to get a matching view. 2, we use oracle and this db uses views not optimaly - when you run a select query, the view is first created and then your query is run against the view - this is a big performance hit.

A view's sql is inlined in the query, unless you're using an old version of oracle. You can also use indexed views to overcome this.

I don't understand why we need to discuss our architecture decisions,

Because you ask us for a solution but the easy solutions we gave you weren't doable according to you (as the easy solution, our authorization system is, you said, too much work, which I disagree with) and in the end it will turn out to be a weak spot of our framework while that is in reality not the case. Also you didn't answer my question about the storage of the row security which is essential for your problem. I asked this because a requirement to have for every row in every table a second row somewhere which will show if the initial row is fetchable is either actually pretty straight forward (e.g. very simple value testing and the range of values is very small) or it's a deep group based security system and what you want with tables is not really something doable in practice as maintenance of these security table data is very complex so that's why I asked how you want to do this.

i have described a senario we're trying to achieve, please help me get it done.

We do, but all options we've given are not sufficient according to you, so there's little room left for us. As I said, you can append sql to queries, you can always do that, but it's very very cumbersome to make that fail safe (due to the aliasing). It's error prone with linq as aliases won't match in a lot of cases and this will result in you running into problems with crashes at runtime and 10 to 1 these will very hard to track down and will be posted here as "some query crashes" which will take a lot of time to solve. I want to avoid all that. I can assure you, appending sql to the SQL generated from linq queries is not going to be easy, and will fail in a lot of situations, however it will be hard to track down if it's your (your appender is wrong) or our fault (bug in linq provider).

We added a deep authorization system to our entity system some time ago and it works at every level, even field value read/write and in databinding, precisely for situations like the one you're in: fine-grained security throughout the application at the entity level. I really fail to see how this is 'more work', as it's simply not more work (likely 1 class you can share among many ifnot all entity classes). It's not something that works in the db, true, but why does it have to work in the DB ? You consume the data OUTSIDE the db and saves/deletes have to be taken into account as well for security, or are you doing those through stored procs?

this senario is not out there for my opinion, it is legitimate to want to add a join + filter to another table when executing a select statement using the tool's object model, don't you think?

sure, specify the join in the query. But you want to add it somewhere in the pipeline and there it goes wrong.

What if the table you've to join with has alias LPA_L1, and is in a derived table 3 levels deep? How are you going to add that join to that table? And what will the filter look like?

I ask these questions to get an overview to see if there are alternatives. As I said earlier, it's very very cumbersome to do this the way you want to do this, which is the most hardest way to get this done, if possible at all.

I understand you're in a tough position right now, but so am I: you ask us for help, we try to give that to you, but these options aren't sufficient so it leaves us with one other option: it won't work in your situation. That will likely cause a big problem for you and your project, and I don't want to take the responsibility for that when our framework can't do something you want and the project fails.

If you must, in Adapter, you can intercept anything, every query being generated you can intercept and adjust, just look at the reference manual and then DataAccessAdapterBase class. You can also decide to use your own version of the Oracle DQE (as the sourcecode is at your disposal) and try to append tables there. I can assure you though, with linq queries, it's not going to be solid in many cases, because you first have to determine the alias of the table you've to join with and place the table with the security info INSIDE the right derived table.

The bottom line however is: how are you going to filter row R in table A based on some data in table B and how do A and B relate? I also really would like to know why our authorization system is more work than all of this?

though, if we try to advise you something, and you say it's not a good alternative because there's 2 months till a deadline, that's a bit odd, as you should have taken this into account from the get go. Appending something to a query and assume that will work in all occasions is not matching reality for whatever framework you'll use, as the query you're appending to can have unlimited different forms.

That might be odd ...

I also really would like to know why our authorization system is more work than all of this?

the situation is as follows: the project is by a big part a migration of an older system that was based on stored procedures. for the older system, all filters were applied at the sp's level.

the requirenmet to filter according to a field's value that exists at another table might be a poor schema design but that is our situation. that is the reasone we can't use the authorization system, it's just a hoge concept change and is too risky at that stage.

A view's sql is inlined in the query, unless you're using an old version of oracle.

we do use an old version, we can't change it becouse of a cheep customer .

additional info -

the column we are filtering against represents an organization unit of the user that has created the record\entity. when a user displayes a content of a table, we need to filter the table and remove records that were created by a different organization unit then the user viewing the table.

hope i have shed some light on the sitiation

thanks again otis and please don't be mad at me, i'm just trying to make a living here

the requirenmet to filter according to a field's value that exists at another table might be a poor schema design but that is our situation. that is the reasone we can't use the authorization system, it's just a hoge concept change and is too risky at that stage.

I'd say the Authorization would be a perfect choice here, please have a look at it. Possibly it will turn out to be much easier to implement.

I agree with Walaa, I think it will be really easy. The thing to consider is this. If I have a Customer table and with that table a security table Security, the rows in these two tables are related otherwise you couldn't join them either.

So some data in customer is tied to some data in Security, e.g. a user ID. That user ID is then stored with an organisation in the Security table.

What you do is you'll cache the security data in a central object, at startup. In your authorizer you'll get a call if an entity is allowed. You examine the entity data, e.g. the USerId field. You ask the cached data object if the userid is allowed, and if not, you simply deny the entity and it will either be empty or thrown away depending on the setting in the authorizer. if you have in each table the same 'UserId' field you can just use the same authorizer in all entities.

Please do some testing with this, it will likely turn out to be very simple, also because the security data is likely not very big, so easily cached.

I'd say the Authorization would be a perfect choice here, please have a look at it. Possibly it will turn out to be much easier to implement.

Hi walaa,

i have read the chapter "Generated code - Setting up and using Authorization". according to the description and examples, we can determine if an entity will be loaded after the entity has been already fetched from the db.

in our application, a lot of queries returns hundreds of thousands of records, the whole record set will be returnd and filtered in memory - this will be a performance hit.

also - as i mentioned, we need to filter according to a related record at a related table (join), using the Authorization solution, i'll need to query the db for each entity, again a huge performance hit.

Again, thanks for all the help.

also - as i mentioned, we need to filter according to a related record at a related table (join), using the Authorization solution, i'll need to query the db for each entity, again a huge performance hit.

Frans (Otis) in his last post was just explaining how you'd cary this out without querying the database for each entity (by caching).

That's the same technique used in this forum. And you can see it's very fast.

Btw, the Forum is Open Source.

Hi,

i haven't notice Otis's post when i've posted mine.

the field we look for is not the user id, it is the organization unit that a certain entity (bank account for example) is related to. most of the bank accounts are created by a batch process that runs under a certain user account. that means that we can't determin the organization unit of an entity based on the user's id only.

so this is an example to why the authorization solution will not work.

I'm sorry for being such a pain about this

Hi Avi,

Ok, I will try to resume what had been discussed in this thread. So far, you have these options:

Authorization I really think this is the less-effort and more clean option. I know what you mean by performance hit but you should give it a spin at least to see if it is worth to use. And, your scenario is good candidate for this. It work with your OrganizationId or UserId, or whatever you decide, the logic is up to you.

Add the filter in every linq code This is, write down the filter in the normal linq2llbl queries. I know, this scares you. Think again, this is more or less what the architectural design left to you to do. Not mentioned before but indeed this is an option if you have two months to deadline and this kind of challenge needed.

Intercept queries and put your filter there Again, this could be done but might be complex to achieve and not a full consistence solution. If the expressions would be written using the normal api (RelationPredicateBucket, Relations, SorExpressions, etc) the integration wouldn't be that hard, but you are using linq2llbl, and this uses more advance constructions and aliases.

So, the options are there, please consider (and test) the two other options before going to intercepting queries.
At the end of the day each option has a downside to you, so you have to choose the less bad option. Whatever you choose, we will try to help you . So let us know what you decide.

Hi daelmo and thanks for your help,

The best solution for us is option 3 - intercepting the queries.

i understand you cannot promise this solution will be complete but we want to try it.

the problem there are the aliases that are added when you use linq2llblgen. can you tell me what component adds\determines the aliases? can i plug in the pipeline and change the alias to be the table's real name?

thanks.

mma02 wrote:

Hi daelmo and thanks for your help,

The best solution for us is option 3 - intercepting the queries.

i understand you cannot promise this solution will be complete but we want to try it.

the problem there are the aliases that are added when you use linq2llblgen. can you tell me what component adds\determines the aliases? can i plug in the pipeline and change the alias to be the table's real name?

thanks.

Some important things first: - IF you decide to do this, and queries crash due to some alias error, and you report these, you have to mention you are modifying the query yourself. This is very important otherwise we'll be investigating errors that aren't there. I'm reluctant to help you with this point because it will likely end up in tears.

• You haven't provided example data. I still am convinced what you want is solveable with other means than appending predicates to the query. So please specify a normal record in a table which has to be filtered and the security data it is filtered by plus the relationship between them. The 'bank account' in your example apparently is associated with an organisational unit. That's ok, but isn't that simply a problem of the query? I.o.w. you want all accounts for a given organisational unit, filter on it, so normal business logic. Also, the association between bank account and organisational unit is determined somehow. You still haven't explained how, while I've asked you to do that a couple of times. You have to understand that I'm not really happy about that.

The linq provider calls two different paths. With every path, you have to alter the query elements before they become SQL. Expect deep, complex graphs but as I said earlier, this is the hardest route to choose. - FetchEntityCollection, if the query produces entities. You have to override FetchEntityCollection in DataAccessAdapter, in a partial class. There you have to append whatever you want to the elements specified. be aware that this is likely not going to get easy but as you haven't specified specifics, I can't help you further.

• FetchProjection when you fetch a custom projection. This isn't a virtual method and very problematic to append to. However as this is a projection which is custom, you have to filter on the related data inside the query anyway, so appending the filter in the query itself in code is IMHO easier.