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
|
Hi I used http://www.sqltolinq.com/ to help me generate some linq. It works (I think) in LinqToSQL, however not in Lightspeed. Perhaps there is a better way to write this linq query for LS?
var listOfCampaignsMerchantIsInvolvedIn =
(from merchantgroupactivity in uow.MerchantGroupActivities
from merchantgroupmerchant in uow.MerchantGroupMerchants
where merchantgroupmerchant.MerchantU.Id == merchantUIDGuid ** this row isn't producing correct SQL**
select new
{
}).Distinct();
which produces this SQL from Tracelogger:
SELECT
t0.MerchantGroupActivityUID AS [t0.MerchantGroupActivityUID],
t0.ActivityUId AS [t0.ActivityUId],
t0.MerchantGroupUId AS [t0.MerchantGroupUId],
t1.MerchantGroupMerchantUID AS [t1.MerchantGroupMerchantUID],
t1.MerchantGroupUId AS [t1.MerchantGroupUId],
t1.MerchantUId AS [t1.MerchantUId],
t2.MerchantUID AS [t2.MerchantUID],
t2.Active AS [t2.Active],
t2.Id AS [t2.Id],
t2.LockVersion AS [t2.LockVersion],
t2.Name AS [t2.Name],
t2.Password AS [t2.Password],
t2.RedemptionDisplayMessageDefault AS [t2.RedemptionDisplayMessageDefault],
t2.TimezoneUId AS [t2.TimezoneUId],
t2.TransactionLimit AS [t2.TransactionLimit]
FROM
MerchantGroupActivity t0
CROSS JOIN
MerchantGroupMerchant t1
INNER JOIN
Merchant t2
ON
t1.MerchantUId = t2.MerchantUID
WHERE
t1.MerchantGroupMerchantUID = '9a9b8dea-e250-xxxx-yyyy-7e3dfd171df8' ** wrong I want Merchant.MerchantUID***
|
|
|
|
|
Can you please send through your model (or even a cut down form containing the entities above) and we can have a look at this.
Thanks! Jeremy |
|
|
|
|
Can you also make sure you're running against the latest nightly build Dave? Just in case we've fixed something here. Cheers, - JD |
|
|
|
|
Hi JD Build was a few nights ago.. Just installing last nights build.. and.... same. Cheers Dave |
|
|
|
|
Hi Dave, As above - could you send through your model and we can have a look into this :)
Thanks! Jeremy |
|
|
|
|
Thanks for sending through the model, we have corrected the issue around the generated SQL - the issue was around how the criteria was being optimized (in this case not very well!). You will notice that the generated SQL now will actually use an EXISTS clause - this is currently standard when we have traversals in criteria within LightSpeed. If you want to generate the original statement you were looking for you should write the LINQ statement like this: var listOfCampaignsMerchantIsInvolvedIn =
(from merchantgroupactivity in unitOfWork.MerchantGroupActivities
from merchantgroupmerchant in unitOfWork.MerchantGroupMerchants
join merchant in unitOfWork.Merchants on merchantgroupmerchant.MerchantUId equals merchant.Id
where merchant.Id == Guid.NewGuid()
select new
{
merchantgroupactivity.ActivityU.CampaignU.Id
}).Distinct();
Jeremy |
|
|
|
|
Many thanks - great support. Regards Dave |
|
|
|
