- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Special condition in JOIN clause
Joined: 17-Jan-2012
Hi,
I absolutely need to use a condition within the JOIN clause. But it is a variable from a parameter in C # and not a definition of fields.
Here is the table definition
Activite
{
actCode INT
actTitle NVARCHAR(130)
actProjectCode INT
actLeft INT
actRight INT
...
}
User
{
uCode ID
uBirthday DATETIME
uFirstName VARCHAR(200)
uLastName VARCHAR(200)
...
}
UserActivite
{
uaUserCode INT -- User code
uaActCode INT -- Activity code assigned to user
...
}
Experimentation
{
expCode INT -- Experimentation code
expActCode INT -- Activity code
}
Here is the Linq code that I've done so far :
Int32? UserCodeQueryAccess = 55; // Can be NULL or having a number
Int32 ProjectCode = 32 // Must have a valid number
DataAccessAdapter adapter = new DataAccessAdapter();
LinqMetaData lmd = new LinqMetaData(adapter);
var query = (from tActivite in lmd.Activite
join tUserActivite in lmd.UserActivite on tActivite.ActCode equals tUserActivite.UaActCode into groupJoinUserActivite
from gUserActivite in groupJoinUserActivite.DefaultIfEmpty()
/*############# PROBLEM #1 #################*/
join tUserActivite in lmd.UserActivite on new { UaUserCode = UserCodeQueryAccess, UaActCode = tActivite.ActCode } equals new { tUserActivite.UaInterCode, tUserActivite.UaActCode } into groupJoinUserActivite
from gActiviteWithParent in groupJoinActiviteWithParent.DefaultIfEmpty()
join tExperimentation in lmd.Experimentation on gActiviteWithParent.ActCode equals tExperimentation.ExpActCode into groupJoinExperimentation
from gExperimentation in groupJoinExperimentation.DefaultIfEmpty()
where tActivite.ActProjectCode == ProjectCode
group new { gActiviteWithParent, gExperimentation } by new
{
gActiviteWithParent.ActCode,
gActiviteWithParent.ActProjectCode,
gActiviteWithParent.ActLeft,
gActiviteWithParent.ActRight,
...
} into g
select new
{
g.Key,
/*############# PROBLEM #2 #################*/
TotalExperimentations = g.Count(p => p.gExperimentation.ExpCode != null),
}
).ToList();
Problem #1 (Look in the code where it is written Problem # 1)
I need to include a variable in my join condition. But the compiler complain "The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.
I know that field UaInterCode is a Int32, but under some circumstance I need to be able to pass a NULL value sometime and other time a valid number.
This condition absolutly need to be in the JOIN clause, I can't put it in the WHERE clause because the query result will be good..
I tried a thousand way but I give up I hope someone here can help me.
Problem #2 (Look in the code where it is written Problem # 2)
I want to compute all the experimentation inside the gActiviteWithParent. Normally with MS SQL Server I just did this in the SELECT containing the activity-experimentation join :
COUNT(Experimentation.expCode)
But I notice that doing (!= null) will throw away row having no experimentation. And I don't want that. Is there a way to get experimentation count for each activity without altering current activity list ?
Also, it is possible to put a condition in this COUNT ? Exemple : Count experimentation only for activity havinga condition like this :
ActLeft = ActRight = 1
Best regards, Martin
What's IntervenantActivite? You didn't explain its relation with other entities.
Could you please build an example over Northwind for simplicity, and explain what are you trying to do from the business perspective, i.e. what are you trying to fetch.
And let's tackle each problem at a time.
Thanks.
Btw 'The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'' is a compiler error caused by your query, not by our framework.
Joined: 17-Jan-2012
Hi Walaa,
I edited my first post, sorry for the incorrect type (Intervenant). It is because I speak French and I try to translate my query with English terms (User), so that you understand better.
I don't think I can transpose this query problem to the Northwind database because it is not the same context. But if you not understand I will I wil give a try in a next post.
First, you must know that I am trying to migrate a project that already exists and I get the same results as the existing queries.
So for this problem we have 5 logical entity.
- Project
- Activity
- Experimentation
- User
- UserActivite
# A project contains activities. But we do not need the table for this problem.
# An Activite is an Activity. Kind of operation... An Activity can have 0 or more sub-activities. But it can also have 0 or more experimentations. When it has sub-activities, it can not have experimentaions.
# Experimentation is an experiments.
# User is a common user.
# UserActivite indicates whether the user has access to an activity. The activities are trees, so they have a LEFT and RIGHT. ("Nested Set Model").
Now what I'm trying to do (important : In one query), is 1 - Get all activities that user is assigned.
2 - From these activites assigned, I need the get the full path to the first node parent, so I need to join again with the Activite table.
3 - Now I have the each full path of all activite that user is assigned. From there I need to get the number of experiments assicated to each activite.
4 - The final result will be a list of Activite (including full path to their parent), plus the number of experiments for all.
Why the join condition "UserCodeQueryAccess" can be null it is because I need to display this list inside a GridView, and it depend who is askin for the list. If it is an Admin then the code will be NULL, if it is a standard user then the code will be a valid number. When it is an admin the all activities will be shown. When it is a standard user, then only the activity assigned will be retreived.
If I put the condition in the WHERE clause, then the result will not be the same. I will loose the full path of the activity to their parent. And I don't want that. The condition must be in the JOIN clause.
The second probleme is getting the COUNT of each experiments for all activities. I don't understand how to do it without altering the result. Doing that
TotalExperimentations = g.Count(p => p.gExperimentation.ExpCode != null),
Will append this in my query
SELECT
...
COUNT(*)
FROM ...
WHERE expCode IS NOT NULL
This is not what I need, I want
SELECT
...
COUNT(expCode)
FROM ...
WHERE ...
Hope you will understand
Joined: 17-Jan-2012
Otis wrote:
Btw 'The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'' is a compiler error caused by your query, not by our framework.
If the clause in the join is on two System.Int32, then comparing a C# System.Int32 variable could work ?
But what If I try to compare using System.Int32? (nullable type) ? In SQL this can be done.
Joined: 17-Jan-2012
Maybe if I show you the SQL query it can help you.
// Must have a valid number
DECLARE @ProjectCode INT
SET @ProjectCode = 32
// Can be NULL or having a number
DECLARE @UserCodeQueryAccess INT
SET @UserCodeQueryAccess = NULL
// Can be NULL or having a number
DECLARE @UserCodeViewAsAnotherUser INT
SET @UserCodeViewAsAnotherUser = 55
SELECT actCriteriaWithParent.actCode,
actCriteriaWithParent.actProjectCode,
actCriteriaWithParent.actLeft,
actCriteriaWithParent.actRight,
-- Number of experimentation for each activites
COUNT(expCode) AS TotalExperimentation,
-- User is assigned to this activity or one of more of their sub-activities
CASE
WHEN @UserCodeViewAsAnotherUser IS NULL OR COUNT(UserIsAssigned.uaInterCode) > 0 THEN
1
ELSE
0
END AS UserIsAssigned
FROM Activite AS actCriteria
LEFT JOIN UserActivite AS UA1
ON UA1.uaInterCode = @UserCodeQueryAccess
AND UA1.uaActCode = actCriteria.actCode
RIGHT JOIN Activite AS actCriteriaWithParent
ON actCriteria.actProjectCode = actCriteriaWithParent.actProjectCode
AND actCriteria.actTreeRank = actCriteriaWithParent.actTreeRank
AND actCriteriaWithParent.actLeft <= actCriteria.actLeft AND actCriteriaWithParent.actRight >= actCriteria.actRight
LEFT JOIN Experimentation
ON expActCode = actCriteriaWithParent.actCode
-- The user requesting the list of activities can be an administrator. In this case, it must provide to see all activities.
-- However, it may ask to see the activity as if it were another user. So in this case, a field must be added to the table
-- that specifies whether the worker for whom it is past, would have access to cettte activity.
LEFT JOIN Activite AS childActivityViewAsOtherUser
ON childActivityViewAsOtherUser.actProjectCode = actCriteriaWithParent.actProjectCode
AND childActivityViewAsOtherUser.actTreeRank = actCriteriaWithParent.actTreeRank
AND childActivityViewAsOtherUser.actLeft BETWEEN actCriteriaWithParent.actLeft AND actCriteriaWithParent.actRight
AND childActivityViewAsOtherUser.actLeft = childActivityViewAsOtherUser.actRight - 1
LEFT JOIN UserActivite AS UserIsAssigned
ON UserIsAssigned.uaInterCode = @UserCodeViewAsAnotherUser
AND UserIsAssigned.uaActCode = childActivityViewAsOtherUser.actCode
WHERE (actCriteria.actProjectCode = @ProjectCode)
AND (@UserCodeQueryAccess IS NULL OR UA1.uaInterCode IS NOT NULL)
GROUP BY actCriteriaWithParent.actCode,
actCriteriaWithParent.actProjectCode,
actCriteriaWithParent.actLeft,
actCriteriaWithParent.actRight
I just have that two problems and once they settle, it allows me to move a lot in my project migration. Since I have the same problems with others queries at different place in project.
Best regards, Martin
Ok, About your first problem, I think the reason is that: the types and the names in the join must match. Try this:
...
join tUserActivite in lmd.UserActivite
on new { UaUserCode = UserCodeQueryAccess, UaActCode = tActivite.ActCode }
equals new { UaUserCode = tUserActivite.UaInterCode, UaActCode = tUserActivite.UaActCode }
into groupJoinUserActivite
...
Joined: 17-Jan-2012
And for the second problem, do you plan to extend linq to support a function like LeftJoin and RightJoin ?
DataObject.Net has implement this functionnality and it practical for people like me that want to use only linq. But as we have a licence with you we cannot go with DataObject.Net.
And maybe the possibility to put use join condition with different operator like Greater, GreaterThanEqual, Smaller, SmallerThanEqual, Like, etc... ?
DataObject.Net is working on these join operator and plan this new feature in about two months...
erakis wrote:
And for the second problem, do you plan to extend linq to support a function like LeftJoin and RightJoin ?
DataObject.Net has implement this functionnality and it practical for people like me that want to use only linq. But as we have a licence with you we cannot go with DataObject.Net.
And maybe the possibility to put use join condition with different operator like Greater, GreaterThanEqual, Smaller, SmallerThanEqual, Like, etc... ?
DataObject.Net is working on these join operator and plan this new feature in about two months...
They need 2 months for an extension method?
Anyway, left/right joins are auto-generated in linq in a some cases. The usage of 'join' in a linq query always results in an inner join, as the linq system demands that ('join' is defined that way). However, this results in a left join if the FK is nullable:
var q = from o in metaData.Order
where o.Customer.Country=="Germany"
select o;
Here, you navigate in the where clause over a relationship, order m:1 customer, from fk to pk side, and this results in a join being done in the query to make the where clause possible. As the FK CustomerId in order is nullable, this results in a left-join towards the FK side, or Customer RIGHT JOIN Order.
If the FK isn't nullable, inner and left/right joins are equal if your intention is to join in the FK sides which have no pk side, as there's no optional FK side. The runtime framework switches to right joins if required for the query, so you should simply focus on left joins and leave the right-joins to the framework. For example, if you want to accomplish: Customer LEFT JOIN Order RIGHT JOIN Employee, you can simply define this with Customer - Order (left join) and Employee - Order (left join) and the framework will switch Employee - Order to right join, as 'order' is already in the join list.
To explicitly create a left-join in linq you have to use the silly DefaultIfEmpty() clause on a join ... into construct. This query is equal to the one above:
var q = from o in metaData.Order
join c in metaData.Customer on o.CustomerId equals c.CustomerId into oc
from x in oc.DefaultIfEmpty()
where x.Country=="Germany"
select o;
This 'defaultifempty()' is of course silly, but it's how linq works. What you should do however is use navigations (o.Customer.Country ) in case you want to use joins if the joins are to be left joins.
If I want to get all customers LEFT JOIN order, it's a different query, and you have to use the join .. into + defaultifempty() clause. This is something you'll get the hang of. The query becomes:
// get all customers (pk side) which don't have an order
var q = from c in metaData.Customer
join o in metaData.Order on c.CustomerId equals o.CustomerId into co
from x in co.DefaultIfEmpty() // x is of type order
where x!=null
select c;
We didn't add the leftjoin/rightjoin extension methods (although we could have) as it's not a standard linq construct. Left/right joins are something which sucks in linq but at the same time, it's explained very well in general linq documentation and books. Forcing users to use an extension method means they'll learn non-standard things, while using linq, something we tried to avoid (unless necessary, like missing features like CountColumn and TakePage)
So rule of thumb:
var q = from toFetch in metaData.EntityToFetch
join related in metaData.EntityRelated on toFetch.PkField equals related.FkField into variable
from relatedFromVariable in variable.DefaultIfEmpty()
where ....
select toFetch;
You can also construct joins by using 'nested from' clauses:
var q = from c in metaData.Customer
from o in metaData.Order
where c.CustomerId == o.CustomerId
&& c.Country=="UK"
select o;
though these are never going to be left/right joins, always inner. However, this construct makes it possible to construct on clauses (as 'where') which contain an expression other than equals.
But it has its drawbacks: nested froms lead to cross-joins which can't always be optimized away by the RDBMS, resulting in loss of performance.
Instead of patching linq's flawed syntax (which is impossible, as some flaws are deeply rooted in microsoft's design), we designed a new query system, with a fluent language, which is close to SQL and still typed: QuerySpec (see http://weblogs.asp.net/fbouma/archive/2011/04/21/llblgen-pro-queryspec-the-basics.aspx ). QuerySpec gives you all the power you need with .NET 3.5+ constructs and you can do more in queryspec than you can in Linq and write compact queries, so you don't have to fall back onto our verbose predicate system
QuerySpec is already in beta for a long time and mature, so as a licensee you can start right away (it's available in the customer area). We rolled it into v3.5, which will go into beta early next week. QuerySpec allows you to construct left/right joins with custom On clauses without problems.
Hope this helps to ease the linq pain a bit.
Joined: 17-Jan-2012
Otis,
Wow what a great explanation about join. I will have to read it about 3 or 4 times again before all understand but in one word : Thanks
I think too that Linq language is missing many characteristics. It could be a great intermediate between front-end and the database. But I don't find it friendly user compared to the SQL language. In addition, we never know how our query will be generated and it is frustratin. Good thing you did the QueryProfiler, nice job !
Another thing that is annoying me. Do you plan supporting more operation on TimeSpan and DateTime ? Like subtracting two date and get the difference in TotalMinutes. I know that I can do a database function mapping but if I do that I will break the compatibility with other RDBMS like MySQL.
Next thing, with QueryPath is it possible to directly apply other operator on join condition, like greater or greater or equal ? Now with Linq I have to put into the where clause, doing a cross-join and the use this query as a subquery. This is not optimal... And it is easy to do nested query ?
About QueryPath, do you plan to release it or it will stay in beta mode for several more month ?
erakis wrote:
Another thing that is annoying me. Do you plan supporting more operation on TimeSpan and DateTime ? Like subtracting two date and get the difference in TotalMinutes. I know that I can do a database function mapping but if I do that I will break the compatibility with other RDBMS like MySQL.
Followed here: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=20608
erakis wrote:
Next thing, with QueryPath is it possible to directly apply other operator on join condition, like greater or greater or equal ? Now with Linq I have to put into the where clause, doing a cross-join and the use this query as a subquery. This is not optimal... And it is easy to do nested query ?
I think you meant QuerySpec
Yes, you can do these kind of queries:
var qf = new QueryFactory();
var q = qf.Create()
.Select(() => new
{
CustomerId = CustomerFields.CustomerId.ToValue<string>(),
EmployeeId = EmployeeFields.EmployeeId.ToValue<int>(),
LastName = EmployeeFields.LastName.ToValue<string>()
})
.From(qf.Customer.InnerJoin(qf.Employee).On(StringFunctions.Char(CustomerFields.CustomerId, 0).Equal("C")));
...
var qf = new QueryFactory();
var customerCountQ = GetCustomerCountsPerCountryData(qf);
var q = qf.Create()
.SelectFrom(customerCountQ)
.From(qf.Customer.InnerJoin(customerCountQ).On(CustomerFields.Country.Equal(customerCountQ.Field("Country"))))
.Where(customerCountQ.Field("NumberOfCustomers").GreaterThan(5));
var qf = new QueryFactory();
var q = qf.Employee
.From(QueryTarget.LeftJoin(qf.Employee.TargetAs("m1")).On(EmployeeFields.ReportsTo == EmployeeFields.EmployeeId.Source("m1"))
.LeftJoin(qf.Employee.TargetAs("m2")).On(EmployeeFields.ReportsTo.Source("m1") == EmployeeFields.EmployeeId.Source("m2")))
.Where(EmployeeFields.EmployeeId == 6)
.Select(() => new
{
id = EmployeeFields.EmployeeId.As("id1").ToValue<int>(),
id2 = EmployeeFields.EmployeeId.As("id2").Source("m1").ToValue<int>(),
id3 = EmployeeFields.EmployeeId.As("id3").Source("m2").ToValue<int>()
});
And non-equi join like this:
var qf = new QueryFactory();
var q = qf.Create()
.Select(EmployeeFields.LastName.SetObjectAlias("e1"))
.From(qf.Employee.As("e1").InnerJoin(qf.Employee.As("e2"))
.On(EmployeeFields.HireDate.SetObjectAlias("e1") > EmployeeFields.HireDate.SetObjectAlias("e2")));
that produces the expected desired sql:
SELECT
[LPA_e1].[LastName]
FROM
( [Northwind].[dbo].[Employees] [LPA_e1]
INNER JOIN
[Northwind].[dbo].[Employees] [LPA_e2] ON [LPA_e1].[HireDate] > [LPA_e2].[HireDate])
erakis wrote:
About QueryPath, do you plan to release it or it will stay in beta mode for several more month ?
It will be released in v3.5 which goes on beta next week
To add, you can start with queryspec today, it's in the customer area: betas section under 3.1. The api is equal to what's included in v3.5, so you won't run into breaking code.
Joined: 17-Jan-2012
Otis wrote:
To add, you can start with queryspec today, it's in the customer area: betas section under 3.1. The api is equal to what's included in v3.5, so you won't run into breaking code.
I did some test with QuerySpec. A verry little query :
using (DataAccessAdapter qAdapter = new DataAccessAdapter())
{
var qf = new QueryFactory();
var z = qf.User
.Select(Projection.Full)
.Where(UserFields.Birthday < new DateTime(1975, 01, 01));
var results = qAdapter.FetchQuery(z);
}
The final query result is 26 rows and the database table only contains 50 rows in total.
I notice that it is a bit slow while executing the FetchQuery. About 1 to 2 seconds loose inside this function.
So it is more slow then Linq query. It is normal ?
QuerySpec is faster than our linq provider (not much though, as our linq provider is very fast too) because it requires less processing. Both are way below 1ms for creating SQL from a query, so the slowness might be caused by other means than the API itself or the queryspec query itself, i.e. connection to the DB, the SQL generated results in slow SQL execution etc.
If this is the first query executed in your application, it might be you see a slowness because dependency injection stuff is initialized (if you have that), the physical database connection has to be created (as the pool is empty) etc.
Unless you profile what's going on, it's impossible to know what's causing slow downs.
Btw, your query doesn't need .Select(Projection.Full), you can just append the where to qf.User
See the examples (the unit tests) in the queryspec download.
Joined: 17-Jan-2012
Thansk Otis,
I reboot my computer and now it is OK. This is weird :s
Last question about QuerySpec :
1) Since QuerySpec is in beta and fairly new, is a language test or it will be supported long ?
2) As I understand, QuerySpec is faster than Linq ? Is it the fastest of the three way ? (Linq, QuerySpec and the other using objects like RelationBucket... ?)
3) QuerySpec looks very interesting for my project, it gives me the flexibility I need. So you encourage me to use the QuerySpec to migrate my project NOW ?
Best regards, Martin
erakis wrote:
Thansk Otis,
I reboot my computer and now it is OK. This is weird :s
Not necessarily . What happens with the first connection is that a physical connection is created with the server (SqlConnection does this). This physical connection is a network using object, so it might take more time because of wrong routing somewhere or the server process is busy.
Last question about QuerySpec :
1) Since QuerySpec is in beta and fairly new, is a language test or it will be supported long ?
It's our main query API starting with v3.5. Our framework has a somewhat low-level query system, with predicate objects etc. This system is the base for queryspec and also our linq provider. Both query spec and the linq provider translate the query you specify to the low level objects (predicates, relationpredicatebuckets, entityfield objects etc.). So both rely on the same base.
QuerySpec is designed to be an alternative for Linq, in situations where Linq fails, or is awkward to use, or when you find Linq less appealing because it doesn't look like SQL and queryspec does.
In v3.5, QuerySpec is merged into the runtime libraries project, similar to the linq provider is, and it's part of the runtime framework, and therefore supported like the rest of the runtime framework and as it's part of the runtime framework, it's not going to be pulled out of it, as it's our main query api
2) As I understand, QuerySpec is faster than Linq ? Is it the fastest of the three way ? (Linq, QuerySpec and the other using objects like RelationBucket... ?)
Our SQL generator is very fast, and works with the low level objects like FieldCompareValuepredicate, EntityRelation objects etc. Linq and QuerySpec build on top of that and convert your query to these objects, which are then converted into SQL. Linq expression trees can be very complex and big, and converting them into SQL will then take a lot of tree rewriting and conversion logic. QuerySpec API is very compact and the path from queryspec fragment to low level object is very short. This means it takes less time to convert a queryspec query to the lower level objects than it takes a Linq query to convert to the lower level objects. But don't worry, linq queries still convert to SQL very quickly, and take less than 1ms to convert to SQL (often way less).
So the quickest are the lowlevel objects and method calls (like adapter.FetchEntityCollection()), then queryspec and then linq. As all are very fast, you won't notice any delay from converting to SQL in our framework.
3) QuerySpec looks very interesting for my project, it gives me the flexibility I need. So you encourage me to use the QuerySpec to migrate my project NOW ?
Yes. v3.5, which is a free upgrade for you, has queryspec build in, so you can easily migrate your project to v3.5 when it comes out. If you want to stay with v3.1 for now, that's OK too, the beta of queryspec has the same api (except 2 small methods, but that's minor) as the one in v3.5, so when you migrate to v3.5 in the future, you won't have any breaking changes in queryspec code.
Joined: 17-Jan-2012
Is there a way to intercept the code (lowlevel objects) generated by a QuerySpec ? I don't know all about this low level API and I think I could do a very complexe query using QuerySpec. So as low level language is fastest I could take a look on how the the QuerySpec has been converted to Low level language ?
Because the speed is really important in this project and as I already optimized all of my query it could be nice to get something similar.
Joined: 17-Jan-2012
daelmo wrote:
There is no available Source Code for QuerySpec if that is what you mean.
This is not what I mean. I want to intercept the code of the QuerySpec just after it has been converted to low level language API language.
Queryspec's zip (the beta) has the sourcecode included (see 'source' folder). You can rebuild it and set a breakpoint into the adapter extension method you're calling (remove the strong key reference in assemblyinfo.cs)
Before you spend time on this, be aware that the performance of the query system is very high, meaning that if you rewrite the query in our low level api elements, you might gain a bit, but it won't be that much that it's noticeable, as all systems generate a query in < 1ms.