Please give the code for the next query:

SELECT Table1.Colomn1 AS ColomnA, Table2.Colomn2 AS ColomnB FROM Table2 INNER JOIN Table1 ON Table2.ColomnJoin = Table1.ColomnJoin WHERE (Table2.Colomn3 = XX) ORDER BY Table2.Colomn2

V. 2.6, adapter.

Please can you post what you have tried so far, any issues you are getting, the generated SQL etc.

Have you had a look at the documentation?

Matt

Hi Andre -

If you have a relationship already setup in the database, you have 2 simple options:

1.) You can simply fetch the Entities: * Prefetch * PredicateExpression * SortExpression

2.) You can create a TypedList: This will give you back a DataTable with only fields you want. You can create a TypedList in the LLBLGen Pro designer. Then in code, simply add a PredicateExpression & SortExpression to the TypedList when you do the database call.

Looking these concepts up in the manual will really put you where you want to be. Frans has done a wonderful job writing on just about everything. Thanks, Frans!

Hope this helps -

Ryan

Or if you don't want to use the Designer, you can just use a DynamicList, easy and simple. Please look it up in the manual as well.

MTrinder wrote:

Please can you post what you have tried so far, any issues you are getting, the generated SQL etc.

Have you had a look at the documentation?

Matt

Yes, i have looked at the manual.

Walaa wrote:

Or if you don't want to use the Designer, you can just use a DynamicList, easy and simple. Please look it up in the manual as well.

Yes, i have looked at the manual. If it's so simple I woulnd't ask it.

rdhatch wrote:

Hi Andre -

If you have a relationship already setup in the database, you have 2 simple options:

1.) You can simply fetch the Entities: * Prefetch * PredicateExpression * SortExpression

2.) You can create a TypedList: This will give you back a DataTable with only fields you want. You can create a TypedList in the LLBLGen Pro designer. Then in code, simply add a PredicateExpression & SortExpression to the TypedList when you do the database call.

Looking these concepts up in the manual will really put you where you want to be. Frans has done a wonderful job writing on just about everything. Thanks, Frans!

Hope this helps -

Ryan

My colegue has looked at it and he found the typed list for the group by. I was looking for the groupby and could not find it.

It's not working, but I have a workaround in code, very ugly, but since LLBL is to difficult I have to live with it.

Give a nice example, tell what to to and where. I am not the only one with this question.

My colegue has looked at it and he found the typed list for the group by. I was looking for the groupby and could not find it.

What Group By, your SQL query posted at the start of this thread didn't contain a Group By.

It's not working, but I have a workaround in code, very ugly, but since LLBL is to difficult I have to live with it.

Give a nice example, tell what to to and where. I am not the only one with this question.

Anyway I find your question very strange as the documentation you said you have read already contains such example, with the Group By.

Here are the links for your reference: Using dynamic lists, Adapter Using dynamic lists, Self Servicing

In the example I only find 1 table: Employee Where is the other?

If it is so easy please give the code to make my query in LLBL, I will adapt it to my needs.

Andre1 wrote:

In the example I only find 1 table: Employee Where is the other?

If it is so easy please give the code to make my query in LLBL, I will adapt it to my needs.

I might be missing the point of what you're asking, but I'm using LINQ for most SELECT statements. Here's an example of an actual method that seems close to what you're asking:

    public static EntityCollection<PartyUserEntity> GetPartyUsersByLastName(DataAccessAdapter adapter, string lastName)
    {
        #region Adapter Open
        bool isNewAdapter = false;
        if (adapter == null)
        {
            isNewAdapter = true;
            adapter = new DataAccessAdapter();
        }
        #endregion Adapter Open

        LinqMetaData metaData = new LinqMetaData(adapter);

        var q = from pu in metaData.PartyUser
                 join p in metaData.Person on pu.PartyId equals p.PersonId
                 where p.LastName == lastName
                 orderby pu.Username descending
                 select pu;

        EntityCollection<PartyUserEntity> collection = ((ILLBLGenProQuery)q).Execute<EntityCollection<PartyUserEntity>>();

        #region Adapter Close
        if (isNewAdapter)
            adapter.Dispose();
        #endregion Adapter Close

        return collection;
    }

SELECT Table1.Colomn1 AS ColomnA, Table2.Colomn2 AS ColomnB FROM Table2 INNER JOIN Table1 ON Table2.ColomnJoin = Table1.ColomnJoin WHERE (Table2.Colomn3 = XX) ORDER BY Table2.Colomn2

I cooked the following Northwind example for you:

            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(CustomersFields.ContactName, 0, "CustName");
            fields.DefineField(OrdersFields.OrderDate, 1, "OrdDate");

            IRelationCollection relations = new RelationCollection();
            relations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);


            PredicateExpression filter = new PredicateExpression(); 
            filter.Add(OrdersFields.EmployeeId == 1);

            SortExpression sorter = new SortExpression((EntityField)fields[1] | SortOperator.Ascending);
            // or like this:
            //SortExpression sorter = new SortExpression(OrdersFields.OrderDate | SortOperator.Ascending);

            DataTable dynamicList = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dynamicList, 0, sorter, filter, relations, false, null, null, 0, 0);

Gabbo wrote:

Andre1 wrote:

In the example I only find 1 table: Employee Where is the other?

If it is so easy please give the code to make my query in LLBL, I will adapt it to my needs.

I might be missing the point of what you're asking, but I'm using LINQ for most SELECT statements. Here's an example of an actual method that seems close to what you're asking:

    public static EntityCollection<PartyUserEntity> GetPartyUsersByLastName(DataAccessAdapter adapter, string lastName)
    {
        #region Adapter Open
        bool isNewAdapter = false;
        if (adapter == null)
        {
            isNewAdapter = true;
            adapter = new DataAccessAdapter();
        }
        #endregion Adapter Open

        LinqMetaData metaData = new LinqMetaData(adapter);

        var q = from pu in metaData.PartyUser
                 join p in metaData.Person on pu.PartyId equals p.PersonId
                 where p.LastName == lastName
                 orderby pu.Username descending
                 select pu;

        EntityCollection<PartyUserEntity> collection = ((ILLBLGenProQuery)q).Execute<EntityCollection<PartyUserEntity>>();

        #region Adapter Close
        if (isNewAdapter)
            adapter.Dispose();
        #endregion Adapter Close

        return collection;
    }

By the way, if I were going to only select a couple of fields (rather than the entire LLBL entity, I might add a User class and do the following:

public class User { public int Id {get;set;} public int FirstName {get;set;} public string LastName {get;set;} }

    public static List<User> GetPartyUsersByLastName(DataAccessAdapter adapter, string lastName)
    {

...same as the first...

select new User { Id = pu.Id, FirstName = p.FirstName, LastName = p.LastName };

List<User> list = q.ToList();

...

return list; }

The above is pseudocode but pretty close to what I do when I'm returning a subset of the data for some reason.

thx for all the replies. The Linq code is not the problem, but we are using LLBL. I will adapt the LLBL code for my needs.