Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Joining multiple tables.
 

Pages: 1
LLBLGen Pro Runtime Framework
Joining multiple tables.
Page:1/1 

  Print all messages in this thread  
Poster Message
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 12-May-2008 22:09:29.  
How do you translate the following llblgen query to linq.

Code:

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;
        }

Brandt  Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 13-May-2008 15:51:06.  
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

Code:


var predicate = .......

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




Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 13-May-2008 16:05:16.  
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.

Code:

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 Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 13-May-2008 16:12:36.  
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 Regular Smiley


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.

Code:

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  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 13-May-2008 16:58:45.  
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 Regular Smiley


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.

Code:

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 ?)
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 13-May-2008 18:29:39.  
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



Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 13-May-2008 18:45:26.  
Glad it works Regular Smiley

For reference, it might be good for other readers what the result query looks like in code Regular Smiley Could you post that please ?
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 13-May-2008 18:50:35.  
Otis wrote:
Glad it works Regular Smiley

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



Is this what you were looking for?

Code:

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);}



Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 13-May-2008 18:53:46.  
Yes, excellent Regular Smiley Thanks Brandt Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.