Looking to implement a subquery in a where clause in both Linq and QuerySpec. Specifically, we are looking for the most recent entry for a given object for a given date.

For instance in the code below the following returns the error:

{"An exception was caught during the execution of a retrieval query: The multi-part identifier \"LPLA_8.RecordDate\" could not be bound.\r\nThe multi-part identifier \"LPLA_8.MeterId\" could not be bound.\r\nThe multi-part identifier \"LPLA_8.SysRowState\" could not be bound.\r\nThe multi-part identifier \"LPLA_8.FileDate\" could not be bound.\r\nThe multi-part identifier \"LPLA_8.SysCreatedDate\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}

            var q = from _MeteringData in metaData.TblBillingMeteringData
                    join _Meter in metaData.TblBillingMeter on _MeteringData.MeterId equals _Meter.MeterId
                    join _CreatedBy in metaData.TblUser on _Meter.SysCreatedBy equals _CreatedBy.UserId
                    join _Client in metaData.TblClient on _CreatedBy.ClientId equals _Client.ClientId
                    where _Client.ClientId == ClientId
                        && (_Meter.MeterId == MeterId)
                        && (_MeteringData.RecordDate >= DateFrom && _MeteringData.RecordDate <= DateTo)
                        && _MeteringData.MeteringDataId == (from _MeteringDataCheck in metaData.TblBillingMeteringData
                                                            where _MeteringDataCheck.RecordDate == _MeteringData.RecordDate
                                                            && _MeteringDataCheck.MeterId == _MeteringData.MeterId
                                                            && ((sysRowState == Enumerations.sysRowState.All) ? true : _MeteringDataCheck.SysRowState == (Int32)sysRowState)
                                                            orderby _MeteringDataCheck.FileDate descending, _MeteringDataCheck.SysCreatedDate descending
                                                            select _MeteringData.MeteringDataId).FirstOrDefault()

                        && ((sysRowState == Enumerations.sysRowState.All) ? true : _MeteringData.SysRowState == (Int32)sysRowState)
                        && ((sysRowState == Enumerations.sysRowState.All) ? true : _Meter.SysRowState == (Int32)sysRowState)
                    select new GraphData
                    {
                        label = (System.Convert.ToString(_MeteringData.RecordDate.Year)

                        + "-" +

                                    (_MeteringData.RecordDate.Month < 10 ? "0" + System.Convert.ToString(_MeteringData.RecordDate.Month) : System.Convert.ToString(_MeteringData.RecordDate.Month))

                                    + "-" +

                                    (_MeteringData.RecordDate.Day < 10 ? "0" + System.Convert.ToString(_MeteringData.RecordDate.Day) : System.Convert.ToString(_MeteringData.RecordDate.Day))),

                        value = _MeteringData.DayMeasurement //g.OrderByDescending(x => x.SysCreatedDate).First().DayMeasurement
                    };

  && _MeteringData.MeteringDataId == (from _MeteringDataCheck in metaData.TblBillingMeteringData
                                                            where _MeteringDataCheck.RecordDate == _MeteringData.RecordDate
                                                            && _MeteringDataCheck.MeterId == _MeteringData.MeterId
                                                            && ((sysRowState == Enumerations.sysRowState.All) ? true : _MeteringDataCheck.SysRowState == (Int32)sysRowState)
                                                            orderby _MeteringDataCheck.FileDate descending, _MeteringDataCheck.SysCreatedDate descending
                                                            select _MeteringData.MeteringDataId).FirstOrDefault()

So how to fix this in Linq?

Also have tried something similar in QuerySpec. But received the following error: {"An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a DynamicQuery to a Guid.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}

            var qf = new QueryFactory();
            var q = qf.Create()
                        .From(qf.TblBillingMeteringData.As("D").LeftJoin(qf.TblBillingMeter.As("M")).On(TblBillingMeterFields.MeterId.Source("M") == TblBillingMeteringDataFields.MeterId.Source("D")))
                        .Where((TblClientFields.ClientId.Source("C") == ClientId).And(TblBillingMeterFields.MeterId.Source("M") == MeterId)
                            .And(TblBillingMeteringDataFields.RecordDate.Source("D") >= DateFrom)
                            .And(TblBillingMeteringDataFields.RecordDate.Source("D") <= DateTo)
                            .And(TblBillingMeteringDataFields.SysRowState.Source("D") == (Int32)sysRowState)
                            .And(TblBillingMeterFields.SysRowState.Source("D") == (Int32)sysRowState)
                            .And(TblBillingMeteringDataFields.MeteringDataId.Source("D") == qf.TblBillingMeteringData.As("Check")
                                    .Where((TblBillingMeteringDataFields.MeterId.Source("D") == TblBillingMeteringDataFields.MeterId.Source("Check"))
                                            .And((TblBillingMeteringDataFields.RecordDate.Source("D") == TblBillingMeteringDataFields.RecordDate.Source("Check"))))
                                    //        .OrderBy(TblBillingMeteringDataFields.FileDate.Source("Check").Descending) //.OrderBy(TblBillingMeteringDataFields.SysCreatedBy.Source("Check").Descending)
                                    .Select(TblBillingMeteringDataFields.MeteringDataId.Source("Check").ToValue<Guid>()).Limit(1)

                            )
                        )
                        .Select(() => new GraphData
                        {
                            label = (TblBillingMeteringDataFields.RecordDate.Source("D").As("Date1").ToValue<DateTime>().Year.ToString()

                            + "-" +

                                        (TblBillingMeteringDataFields.RecordDate.Source("D").As("Date2").ToValue<DateTime>().Month < 10 ? "0" + TblBillingMeteringDataFields.RecordDate.Source("D").As("Date3").ToValue<DateTime>().Month.ToString() : TblBillingMeteringDataFields.RecordDate.Source("D").As("Date4").ToValue<DateTime>().Month.ToString()))

                                        + "-" +

                                        (TblBillingMeteringDataFields.RecordDate.Source("D").As("Date5").ToValue<DateTime>().Day < 10 ? "0" + TblBillingMeteringDataFields.RecordDate.Source("D").As("Date6").ToValue<DateTime>().Day.ToString() : TblBillingMeteringDataFields.RecordDate.Source("D").As("Date7").ToValue<DateTime>().Day.ToString()),


                            value = TblBillingMeteringDataFields.DayMeasurement.Source("D").ToValue<Decimal>()
                        }

                        );

            q.From(QueryTarget.LeftJoin(qf.TblUser.As("U")).On(TblUserFields.UserId.Source("U") == TblBillingMeterFields.SysCreatedBy.Source("M")));

            q.From(QueryTarget.LeftJoin(qf.TblClient.As("C")).On(TblClientFields.ClientId.Source("C") == TblUserFields.ClientId.Source("U")));

An understanding of how to do sub (nested) query in both Linq and QuerySpec would be great.

For subquery you could use ".Contains". Check this: http://www.llblgen.com/documentation/5.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_generalusage.htm#string-contains-startswith-and-endswith

Bwt. I dont find relation between your firstand third ode snippet. Please post somthing simpler example to ilustrate what you want to know. If understood, go to a more complex code.

Thanks for the the reply Daelmo.

Lets take a simpler example in T-SQL:

select * from tblBillingMeteringData as a
where a.MeterId = 'E57218B5-46B3-43CD-A3BA-1700566D3EBA'
and a.MeteringDataId = (select top 1 b.MeteringDataId from tblBillingMeteringData as b where b.MeterId = a.MeterId and b.RecordDate = a.RecordDate order by SysCreatedDate desc)
order by a.syscreateddate desc

In this example I am querying a data table by a particular meter id.

I then use a subquery to ensure I only receive the latest entry for each date record.

Finally I order the results by the date created.

Hopefully this is easier to understand. It would be good to have an example in both LINQ and QuerySpec. We have been using Linq however I can see the benefit of the QuerySpec approach.

Cheers

I will post a example in Northwind, which a DB we both know. Suppose I want to generate something like this sql:

SELECT a.* 
FROM Orders a
WHERE a.ShipVia = 1
    AND a.CustomerID = (SELECT TOP 1 b.CustomerID 
                        FROM Orders b 
                        WHERE b.ShipVia = a.ShipVia AND b.OrderDate = a.OrderDate 
                        ORDER BY b.OrderDate)
ORDER BY a.OrderDate

These will be some ways to get it done:

Linq2LLBL

var q = (from o in metaData.Order
            where o.ShipVia == 1 &&
            o.CustomerId == (from o2 in metaData.Order
                            where o2.ShipVia == o.ShipVia &&
                            o2.OrderDate == o.OrderDate
                            orderby o2.OrderDate
                            select o2.CustomerId).First()
            orderby o.OrderDate
            select o);

QuerySpec

var q = qf.Order
.Where(
    OrderFields.ShipVia.Equal(1)
    .And(qf.Order.As("b")
            .Where(OrderFields.OrderDate == OrderFields.OrderDate.Source("b")
                & OrderFields.ShipVia == OrderFields.ShipVia.Source("b"))
            .Select(OrderFields.CustomerId.Source("b"))
            .Limit(1)
            .Contains(OrderFields.CustomerId)));

LLBLGen API

// setup filter
var filter = new RelationPredicateBucket(OrderFields.ShipVia == 1);
filter.PredicateExpression.Add(
    new FieldCompareSetPredicate(
        OrderFields.CustomerId, null, 
        OrderFields.CustomerId.SetObjectAlias("b"), null, 
        SetOperator.Equal,
        OrderFields.ShipVia.SetObjectAlias("b") == OrderFields.ShipVia 
        & OrderFields.OrderDate.SetObjectAlias("b") == OrderFields.OrderDate, null,"", 1, 
        new SortExpression(OrderFields.OrderDate.SetObjectAlias("b") | SortOperator.Ascending)) );

var sorter = new SortExpression(OrderFields.OrderDate | SortOperator.Ascending);

// fetch
var orders = new EntityCollection<OrderEntity>();
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter, 0, sorter);
}