How do you translate the following llblgen query to linq.

ResultsetFields _fields = new ResultsetFields(1);
            _fields[0] = SiteFields.SiteId;

            ISortExpression _sort = new SortExpression(SiteFields.SiteId | SortOperator.Ascending);


            RelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(RegionEntity.Relations.CountryEntityUsingRegionCode, JoinHint.Inner);
            bucket.Relations.Add(CountryEntity.Relations.AddressEntityUsingCountryCode, JoinHint.Inner);
            bucket.Relations.Add(AddressEntity.Relations.SiteEntityUsingAddressGuid, JoinHint.Inner);


            if (!String.IsNullOrEmpty(regioncode))
                bucket.PredicateExpression.AddWithAnd(RegionFields.RegionCode == regioncode);
            if (!String.IsNullOrEmpty(countrycode))
                bucket.PredicateExpression.AddWithAnd(CountryFields.CountryCode == countrycode);

            // exclude decommissioned sites
            if (excludedecommissioned)
                bucket.PredicateExpression.AddWithAnd(SiteFields.Decommissioned == false);

            Dictionary<string, string> _dict = new Dictionary<string, string>();

            // fetch a reader and then add the items to the dict.  use the CommandBehavior.Default because the calling method will handle disposing of the connection
            IDataAccessAdapter adapter = Adapters.InfrastructureCentral;
            using (IDataReader reader = adapter.FetchDataReader(_fields, bucket, CommandBehavior.CloseConnection, 0, _sort, true))
            {
                while (reader.Read())
                {
                    _dict.Add(reader.GetValue(0).ToString(), reader.GetValue(0).ToString());
                }
                reader.Close();
            }
            return _dict;
        }

Basically what I am trying to do with LINQ is multiple join's with conditional where clauses.

The (from s in metaData.Site .....) works fine if you know the exact where clause but since the query expression requires a select or group you can not call the .where extension method after and expect to get correct translated sql. Using the .Join extension only works the first time. When you try it the second time LLBLGEN can not translate the selection into an entity so it fails.

Is there any way to dynamically create a predicate and add it to the query expression like

var predicate = .......

var q = (from s in metaData.Site
             where predicate
             select s.SiteId);

I don't know if the relations are all m:1/1:1 from Site to the other entities. If so, you can add the filters together with the relations in 1 go: q = q.Where(s=>s.Address.Country.Region.RegionCode==regionCode); and you then don't have to add the joins.

If not, you need the joins to be added for every filter. Below I assumed some relations are 1:n, so you needed all joins.

LinqMetaData metaData = new LinqMetaData(adapter);
// first create the base query. 
var baseq = from s in metaData.Site
            orderby s.SiteId ascending
            select s;

// then add the filters, based on variables
var q = baseq
if(!String.IsNullOrEmpty(regioncode))
{
    q = from s in q
        join a in metaData.Address on s.AddressGuid equals a.AddressGuid
        join c in metaData.Country on a.CountryCode equals c.CountryCode
        join r in metaData.Region on c.RegionCode equals r.RegionCode
        where r.RegionCode == regioncode
        select s;
}
if (!String.IsNullOrEmpty(countrycode))
{
    q = from s in q
        join a in metaData.Address on s.AddressGuid equals a.AddressGuid
        join c in metaData.Country on a.CountryCode equals c.CountryCode
        where c.CountryCode == countrycode
        select s;
}
if(excludedecommisioned)
{
    q = from s in q
        where !s.Decomissioned
        select s;
}

// then add the final projection, just the siteId field;
q = q.Select(s=>s.SiteId);  

At least, I think this will get you very far

Otis wrote:

I don't know if the relations are all m:1/1:1 from Site to the other entities. If so, you can add the filters together with the relations in 1 go: q = q.Where(s=>s.Address.Country.Region.RegionCode==regionCode); and you then don't have to add the joins.

If not, you need the joins to be added for every filter. Below I assumed some relations are 1:n, so you needed all joins.

At least, I think this will get you very far

It is m:1. Each site has one address that has one country that has one region. When I used s.Address.Country.Region the debugger threw an exception and said that Address was null. I will try it again to make sure. What i had done was.

IQueryable<SiteEntity> q = metaData.Site;

q = q.Where(s => s.Address.Country.Region.RegionCode == "NA");

Am i missing anything? I dont' have to use with path do I?

Brandt wrote:

Otis wrote:

I don't know if the relations are all m:1/1:1 from Site to the other entities. If so, you can add the filters together with the relations in 1 go: q = q.Where(s=>s.Address.Country.Region.RegionCode==regionCode); and you then don't have to add the joins.

If not, you need the joins to be added for every filter. Below I assumed some relations are 1:n, so you needed all joins.

At least, I think this will get you very far

It is m:1. Each site has one address that has one country that has one region. When I used s.Address.Country.Region the debugger threw an exception and said that Address was null. I will try it again to make sure. What i had done was.

IQueryable<SiteEntity> q = metaData.Site;

q = q.Where(s => s.Address.Country.Region.RegionCode == "NA");

Am i missing anything? I dont' have to use with path do I?

That is indeed what should be done. That where statement will add the required relations to the query automatically.

When you use that, it doesn't work? (I.e. does it crash with a bad query or does it crash earlier ?)

It works. I can't explain why it failed this first time. The only difference is that i updated the code base to the latest 2.6 beta. Thanks for your help as always Otis.

-Brandt

Glad it works

For reference, it might be good for other readers what the result query looks like in code Could you post that please ?

Otis wrote:

Glad it works

For reference, it might be good for other readers what the result query looks like in code Could you post that please ?

Is this what you were looking for?

public virtual Dictionary<string, string> GetSiteDictionaryByRegionAndCountry(string regionCode, string countryCode, bool excludeDecommissioned, DataAccessAdapter adapter)
        {
            LinqMetaData metaData = new LinqMetaData(adapter);

            IQueryable<SiteEntity> q = metaData.Site;

            if (!String.IsNullOrEmpty(regionCode))
                q = q.Where(site => site.Address.Country.Region.RegionCode == regionCode);

            if (!String.IsNullOrEmpty(countryCode))
                q = q.Where(site => site.Address.Country.CountryCode == countryCode);


            return q.OrderBy(site=>site.SiteId).ToDictionary(site => site.SiteId, site => site.SiteId);}

Yes, excellent Thanks Brandt