Dear All

I am new to LINQ 2 LLBGENPRO, and I am trying to do a query to retreive a list of Clients from the Clients table according to the Username and the LanguageCode.

My scenario is this.

I have a Clients Table linked to the CountriesByConsultants by the IDClient, and then the CountriesByConsultants table is linked to the ClientRegionLang by the CountryCode.

So I have the following query which is not working:-

    private List<ClientEntity> GetClientEntity(IDataAccessAdapter adapter)
    {
        using (adapter)
        {
            LinqMetaData metaData = GetDataContext();

            var ClientEntities = (from c in metaData.Client 
                     select c).WithPath<ClientEntity>(cpath => cpath.Prefetch<CountriesByConsultantEntity>(cbc => cbc.IDClient)
                     .SubPath(opath => opath.Prefetch<ClientRegionLangEntity>(crl => crl.CountryCode)
                     )).ToList();


            return ClientEntities;
        }

    }

Also I need to add a where clause to this, whereby ConsultantsLogon.Logon == Username, and LanguageCode = 'en'.

Thanks for your help and time

Johann

I'm not sure to which query do you want to apply the filter.

But anyway please check the following example from the manual:

var q = (from c in metaData.Customer
         where c.Country == "Germany"
         select c).WithPath(customerPath=>customerPath
              .Prefetch<OrderEntity>(c=>c.Orders)
                     .SubPath(orderPath=>orderPath.Prefetch(o => o.Employee)));

Which fetches a Customer collection and each Customer.Orders collections and each Order.Employee entity.

Walaa wrote:

I'm not sure to which query do you want to apply the filter.

But anyway please check the following example from the manual:

var q = (from c in metaData.Customer
         where c.Country == "Germany"
         select c).WithPath(customerPath=>customerPath
              .Prefetch<OrderEntity>(c=>c.Orders)
                     .SubPath(orderPath=>orderPath.Prefetch(o => o.Employee)));

Which fetches a Customer collection and each Customer.Orders collections and each Order.Employee entity.

Hi Walaa,

I already looked at that query, and tried to build mine on that, however did not manage to do it.

Your goal is not clear to me: Whether you want to fetch data from the 3 tables and build your objects Graph. Or you want to fetch data from one table (Client) but you want to join to the other tables to filter on some field which exists on the third table (LanguageCode).

Walaa wrote:

Your goal is not clear to me: Whether you want to fetch data from the 3 tables and build your objects Graph. Or you want to fetch data from one table (Client) but you want to join to the other tables to filter on some field which exists on the third table (LanguageCode).

Hi Walaa

Yes I want to fetch data from one table (Client) and join to the other tables to filter on some field which exists on ConsultantsLogon (Logon) and the ClientRegionLang (table) LanguageCode (field)

Then you don't need to use Prefetchaths at all. Just some joins.

You should have something like:

var ClientEntities = from c in metaData.Client 
                join cbc in metaData.CountriesByConsultantEntity on c.Id equals cbc.IDClient
                join crl in metaData.ClientRegionLangEntity on cbc.Id equals crl.CountryCode
                where crl.LanguageCode == "en" 
                && c.AnyField == xyz
                select c;

I'm not sure how ConsultantsLogon.Logon == Username is related to the above mentioned tables, but I think you can work this out yourself after seeing the above code.

Walaa wrote:

Then you don't need to use Prefetchaths at all. Just some joins.

You should have something like:

var ClientEntities = from c in metaData.Client 
                join cbc in metaData.CountriesByConsultantEntity on c.Id equals cbc.IDClient
                join crl in metaData.ClientRegionLangEntity on cbc.Id equals crl.CountryCode
                where crl.LanguageCode == "en" 
                && c.AnyField == xyz
                select c;

I'm not sure how ConsultantsLogon.Logon == Username is related to the above mentioned tables, but I think you can work this out yourself after seeing the above code.

Ah yeah cool, i thought you could not do more than 1 join in LINQ.

Its working now, although not retreiving the correct data, but the LINQ statement is working

Thanks Walaa