I have this in SQL:

SELECT Car.Model, Car.RentedFrom, Car.Price, TypeOfCar.Type FROM Car INNER JOIN TypeOfCar ON Car.CarId = TypeOfCar.Id WHERE Car.ColorId = 2;

I've tried this in code but it isn't working:

EntityCollection<CarEntity> cars= new EntityCollection<CarEntity>(new CarEntityFactory());
            // create the set of excluded fields to fetch, use initializers.   
            IncludeFieldsList fieldsToFetch = new IncludeFieldsList() {CarFields.Model, CarFields.RentedFrom, CarFields.Price, TypeOfCarFields.Type};  
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(TypeOfCarEntity.Relations.CarEntityUsingTypeOfCarId, JoinHint.Inner);
            bucket.PredicateExpression.Add(CarFields.ColorId == 2);
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(cars, fieldsToFetch, bucket);
            carsGrid.DataSource = cars;
            carsGrid.DataBind();

Do I need two entity collections or can one store all the data? I think I need two and if I'm right how will I merge them?

I **must **use entity collections in this task. Can you help me, please?

IncludeFieldsList fieldsToFetch = new IncludeFieldsList() {CarFields.Model, CarFields.RentedFrom, CarFields.Price, TypeOfCarFields.Type};

You can CAN'T include a field of another entity like that. Your options are:

1. Use PrefetchPaths.

2.Fields mapped on related fields. You always need to prefetch path but you can access the related field (TypeOfCar.Type) as if it is on the Car entity.

1. A custom property that access the related field. Same as (2) but you write this property manually.

So, you don't need to use IncludeFieldsList, unless you want to limit the fields fetched for that entity collection (Car).

Please let us know if you need further help.

I'm still missing it. I included prefetch but I'm getting this error:

A field or property with the name 'Type' was not found on the selected data source.

Here's the code I used.

EntityCollection<CarEntity> cars= new EntityCollection<CarEntity>(new CarEntityFactory());
// create the set of excluded fields to fetch, use initializers.
IncludeFieldsList fieldsToFetch = new IncludeFieldsList() {CarFields.Model, CarFields.RentedFrom, CarFields.Price, TypeOfCarFields.Type};
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CarEntity);
prefetchPath.Add(CarEntity.PrefetchPathTypeOfCar);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(CarFields.ColorId == 2);
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(cars, bucket,  0, null, prefetchPath, fieldsToFetch, );
carsGrid.DataSource = cars;
carsGrid.DataBind();

What am I missing?

IncludeFieldsList fieldsToFetch = new IncludeFieldsList() {CarFields.Model, CarFields.RentedFrom, CarFields.Price, TypeOfCarFields.Type};

You can include a field of another entity like that. Your options are:

I guess that was a typo, David ment you can't use a field from another entity in the IncludeFieldsList.

Well, then I'm back at my original question. How can I fetch the data I need and have it bind to datagrid?

Please go back to David's reply. A "field on a related field" with a prfetchpath should be your way.

Ok. Under "Fields under relations" of Car entity I have:

Field Name: CarType 
Relation: Car - TypeOfCar (m:1)
Is Hidden: False
Description: Returns one instance of the entity 'TypeOfCar' which are directly related to the instance of the entity 'Car' where Car.CarId=TypeOfCar.Id

And in my code I throw out IncludeFieldsList line so I have now:

EntityCollection<CarEntity> cars= new EntityCollection<CarEntity>(new CarEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CarEntity);
prefetchPath.Add(CarEntity.PrefetchPathTypeOfCar);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(CarFields.ColorId == 2);
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(cars, bucket, prefetchPath);
carsGrid.DataSource = cars;
carsGrid.DataBind();

Still no good.

A field or property with the name 'Type' was not found on the selected data source.

Ok. Under "Fields under relations" of Car entity I have: Code:

Field Name: CarType Relation: Car - TypeOfCar (m:1) Is Hidden: False Description: Returns one instance of the entity 'TypeOfCar' which are directly related to the instance of the entity 'Car' where Car.CarId=TypeOfCar.Id

Please check this section of the docs: Fields mapped on related fields sub tab

In the tab you mentioned I've added this field:

Field Name: Type .Net Type: System.String Mapped on field: TypeOfCar.Type Is readonly: True

Droped entire code in Page_Load and bind in design mode. I had all the columns I needed. In llblgenpro component I've defined a session parameter (that's were Car.ColorId is coming from) and it showed all the columns field except Type column which is empty.

If I perform mentioned SQL Querry on the db I get results but on the page Type column is empty.

Please disregard my previous post. I'm an idiot. I just needed to think for 2 sec and I maid it.

You guys are a life savers. I would never got this if it were not for your help.

Sorry for taking so much of your time. Thanks again.

Well I have one more thing to sort and that is sorting. I've read the manual and it shows following code under "Generated code - Sorting, Adapter":

SortExpression sorter = new SortExpression(CustomerFields.Country | SortOperator.Ascendi
ng) &
(CustomerFields.CompanyName | SortOperator.Descending);

so I juust changed this

prefetchPath.Add(CarEntity.PrefetchPathTypeOfCar);

with this code below and it throws following error:

ISortExpression sorter = new SortExpression();
sorter.Add(CarFields.Model| SortOperator.Ascending);
prefetchPath.Add(CarEntity.PrefetchPathTypeOfCar, 0, null, null, sorter); 

The multi-part identifier "MyCarApplication.dbo.Car.Model" could not be bound.

In "How to: enable sorting functionality of an entity collection in a bound grid?" its said

The Adapter's EntityCollection class and the SelfServicing entity collection classes implement the IBindingList.SupportsSorting property and the IBindingList sorting functionality. Setting this property to true/True before the collection is bound to a grid, will make sure that the grid will be able to sort the contents of the collection when the user clicks a column header

and that is exactly what I need but I can't find how to do it.

I'm using adapter and would like to use server side sorting. Please help, as this is the last needed peace of the puzzle.

I've also read http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8192 but it doesnt help me.

Adding the sorter to the prefetch path.Add sorts the related collection (TypeOfCar) - as you are trying to sort this by the Car.Model field you are getting the error.

Add the sorter to the Adapter.FetchEntityCollection call instead - this will cause it to sort the Car collection by the Model field.

ISortExpression sorter = new SortExpression();
sorter.Add(CarFields.Model| SortOperator.Ascending);
adapter.FetchEntityCollection(cars, bucket, prefetchPath,sorter);

(from memory, you may need to check the exact overload of FetchEntityCollection to use)

Matt

I still get this error:

The GridView 'carsGrid' fired event Sorting which wasn't handled.

Here is my latest attempt which also fails miserably:

EntityCollection<CarEntity> cars= new EntityCollection<CarEntity>(new CarEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CarEntity);
prefetchPath.Add(CarEntity.PrefetchPathTypeOfCar);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(CarFields.ColorId == 2);
DataAccessAdapter adapter = new DataAccessAdapter();
ISortExpression sorter = new SortExpression(CarFields.Model | SortOperator.Ascending);
// *** Trying with EntityView2 for sorting *****
EntityView2<CarEntity> carsView = cars.DefaultView;
carsView.Sorter = sorter;
adapter.FetchEntityCollection(cars, bucket, 0, sorter, prefetchPath, null);// need this constructor to use sorter
// ******
carsGrid.DataSource = cars;
carsGrid.DataBind();


<asp:GridView ID="carsGrid" runat="server" AutoGenerateColumns="False" AllowSorting="True"  DataKeyNames="Id" >
    <Columns>
        <asp:BoundField DataField="Model" HeaderText="Title" SortExpression="Model" />
        <asp:BoundField DataField="RentedFrom" HeaderText="Rented From" 
            SortExpression="RentedFrom" />
        <asp:BoundField DataField="Price" HeaderText="Price" 
            SortExpression="Price" />
        <asp:BoundField DataField="Type " HeaderText="Type " SortExpression="Type " />
    </Columns>
</asp:GridView>

Result:

The GridView 'carsGrid' fired event Sorting which wasn't handled.

You don't need this:

EntityView2<CarEntity> carsView = cars.DefaultView;
carsView.Sorter = sorter;

as that is for in-memory sorting.

And, due to the error, you have to manually handle the sort event of the grid and fetch again with the sorter.

If you don't want to write a lot of code is better if you use LLBLGenProDataSource2. Please read the documentation about databinding.

Also there are some examples that shows how to do that (http://llblgen.com/pages/examples.aspx). If you need further help we can post some approximate code for you.

Can I add llblgenpro as a component in the designer, set sorting to server side and then in my Page_Load after I fetch the data with my entity class somehow pass the data to llblgenpro component and then bind llblgenpro with the gridview?

If its possible that would be easiest, but I don't believe so as. I have to bind with entity class. If this isn't a possibility could you write me some example so I can finish this. I would really appreciate it. Thx again for all your previous help.

Can I add llblgenpro as a component in the designer, set sorting to server side and then in my Page_Load after I fetch the data with my entity class somehow pass the data to llblgenpro component and then bind llblgenpro with the gridview?

Youshould be using the LLBLGenPro_DataSource, please check this documentation page. Databinding with ASP.NET 2.0, Adapter

If its possible that would be easiest, but I don't believe so as. I have to bind with entity class. If this isn't a possibility could you write me some example so I can finish this. I would really appreciate it. Thx again for all your previous help.

There is some code samples in the above mentioned link. Also for more details and code examples, please check the "ASP.NET 2.0 databinding example" (not the CRUD one), posted on our website's downloads section. It's very descriptive, and shows all the possibilities.

I've setted gridview to bind with llblgenpro component. In the component I've setted up serverside sorting and tryied to force llblgenpro to use my entity class like this:

EntityFactoryTypeName="ACME.Publishing.DAL.FactoryClasses.BookEntityFactory, cars"  

then tried to put my fetching of data in llblgenpro ondatabinding event but since my syntax to tell llblgenpro to use my entity collection is wrong of course it doesn't work.

Anyway although I'm sure you are pointing me in the right direction I am no closer to resolve this issue, I'm just not seeing it. I've gone through the docs(Two way databinding is I believe my scenario) and example you told me to but I can't figure out what I'm supposed to change. I didn't find in that example any page that fetches data into an entitycollection.

If it helps here's my code:

public EntityCollection<CarEntity> cars; 
protected void Page_Load(object sender, EventArgs e)
{
cars= new EntityCollection<CarEntity>(new CarEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CarEntity);
prefetchPath.Add(CarEntity.PrefetchPathTypeOfCar);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(CarFields.ColorId == 2);
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(cars, bucket, prefetchPath);
//*************
LLBLGenProDataSource_cars.EntityCollection = cars;
//*************
}


<asp:GridView ID="carsGrid" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataKeyNames="Id" DataSourceID="LLBLGenProDataSource_cars">
    <Columns>
        <asp:BoundField DataField="Model" HeaderText="Title" SortExpression="Model" />
        <asp:BoundField DataField="RentedFrom" HeaderText="Rented From" SortExpression="RentedFrom" />
        <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
        <asp:BoundField DataField="Type " HeaderText="Type " SortExpression="Type " />
    </Columns>
</asp:GridView>
<llblgenpro:LLBLGenProDataSource2 ID="LLBLGenProDataSource_cars" runat="server"
  DataContainerType="EntityCollection"
    EntityFactoryTypeName="Car.Dealership.DAL.FactoryClasses.CarEntityFactory, Car.Dealership.DAL"  
    AdapterTypeName="Car.Dealership.DAL.DatabaseSpecific.DataAccessAdapter, Car.Dealership.DALDBSpecific" SortingMode="ServerSide">
</llblgenpro:LLBLGenProDataSource2>

I removed gridview DataSourceID in markup and applied it through code like this:

<llblgenpro:LLBLGenProDataSource2 ID="LLBLGenProDataSource1" runat="server"
DataContainerType="EntityCollection"
    EntityFactoryTypeName="Car.Dealership.DAL.FactoryClasses.CarEntityFactory, Car.Dealership.DAL"
    AdapterTypeName="Car.Dealership.DAL.DatabaseSpecific.DataAccessAdapter, Car.Dealership.DALDBSpecific" SortingMode="ServerSide">
</llblgenpro:LLBLGenProDataSource2>

protected void Page_Load(object sender, EventArgs e)
{
...
adapter.FetchEntityCollection(cars, bucket,prefetchPath);
LLBLGenProDataSource1.EntityCollection = cars;
carsGrid.DataSource = LLBLGenProDataSource1.EntityCollection;
carsGrid.DataBind();

If I say "carsGrid.DataSource = LLBLGenProDataSource1;" I don't get the correct data, if I do it as written above I get the data but sorting isn't working.

The GridView 'carsGrid' fired event Sorting which wasn't handled

Please, does anybody has the time and will to show me how to do this?

You don't have to do it that way.

This is standard ASP.NET datasource control databinding, so it works with our datasource the same way as with objectdatasource etc.

What you 've to do is what's described in the link David linked above

i.o.w.: no code: you've just to tie the control and the datasource in the html.

In code, you then can feed it filters and the like. fetching is done for you by the datasourcecontrol. If you want to do fetching yourself, you've to implement event handlers to the various events exposed by the datasource control as described in the help documentation linked above.

See also the two ASP.NET 2.0 databinding examples.

(ps: we do monitor these forums several times a day, so please be patient. Also, please read the documentation we point you to. If the documentation doesn't show code like you're writing and it doesn't work, please try it as it is documented FIRST. )

If I bind gridview to llblgen control then I get all the data in the gridview except Type column which is empty (this column is subtype) and I don't want that since I'm fetching data through filter.

If I try to bind data to control in the code then I get the data I want but sorting in any column isn't working.

I've looked at data-binding examples you mentioned, but I don't understand what am I supposed to do with it since it shows various events being handled in the eventhandler methods.

Also gone through docs and can see it being mentioned in "Setting the data container manually" and "Two way databinding" but can't figure it out.

For my impatience I apologize.

Please, I'm posting my html code below along with codebehind. Can you please tell me more precisely what should I change? This way I get all the data (not filtered) and Type column is empty and throws exception when clicked to sort.

<asp:GridView ID="carsGrid" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataKeyNames="Id" DataSourceID="LLBLGenProDataSource_cars">
    <Columns>
        <asp:BoundField DataField="Model" HeaderText="Title" SortExpression="Model" />
        <asp:BoundField DataField="RentedFrom" HeaderText="Rented From" SortExpression="RentedFrom" />
        <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
        <asp:BoundField DataField="Type " HeaderText="Type " SortExpression="Type " />
    </Columns>
</asp:GridView>

<llblgenpro:LLBLGenProDataSource2 ID="LLBLGenProDataSource_cars" runat="server"
DataContainerType="EntityCollection"
    EntityFactoryTypeName="Car.Dealership.DAL.FactoryClasses.CarEntityFactory, Car.Dealership.DAL"
    AdapterTypeName="Car.Dealership.DAL.DatabaseSpecific.DataAccessAdapter, Car.Dealership.DALDBSpecific" SortingMode="ServerSide">
</llblgenpro:LLBLGenProDataSource2>


protected void Page_Load(object sender, EventArgs e)
{
cars= new EntityCollection<CarEntity>(new CarEntityFactory());
// Doesn't matter if I set it before or after it just isn't working
LLBLGenProDataSource1.EntityCollection = cars;

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CarEntity);
prefetchPath.Add(CarEntity.PrefetchPathTypeOfCar);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(CarFields.ColorId == 2);

DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(cars, bucket,prefetchPath);

LLBLGenProDataSource1.EntityCollection = cars;
LLBLGenProDataSource1.DataBind();

cars= new EntityCollection<CarEntity>(new CarEntityFactory()); // Doesn't matter if I set it before or after it just isn't working LLBLGenProDataSource1.EntityCollection = cars;

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CarEntity); prefetchPath.Add(CarEntity.PrefetchPathTypeOfCar);

IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.PredicateExpression.Add(CarFields.ColorId == 2);

DataAccessAdapter adapter = new DataAccessAdapter(); adapter.FetchEntityCollection(cars, bucket,prefetchPath);

LLBLGenProDataSource1.EntityCollection = cars; LLBLGenProDataSource1.DataBind();

The above code is completely useless. You have been missing the whole point, and it seems you haven't been reading the docs or the ASP.NET example carefully (code, comments and displayed text).

Anyway there are 2 ways to use the LLBLGenProDataSource (LLBLDS for short).

1- By default the LLBLDS have the LivePersistence property set to true, and this has the effect that it lets the LLBLDS fetches the data for you atuomatically, when databinding kicks.

So if you want to use it this way, then in the page_load all you have to do is set the LLBLDS.FilterToUse to pass the appropriate filter.

Also if you want to use a prefetchPath, you should set the LLBLDS.PrefetchPathToUse to the appropriate one.

And then you should NOT fetch any collection, and you should not set the EntityCollection of the LLBLDS or call any DataBind() method.

2- The second way, is to set LivePersistence to false, and then you will have to handle 3 events to fetch the data yourself and set the EntityCollection of the LLBLDS (e.ContainedCollection in the Perform_Select event handler).

I implemented it in the meantime as option 2. Marking it closed. Thank you.

I'm having the same problem... Could you post html + code of the solution!

THX

Or if anyone else could help. This is the code which works and I get a book named "Winter" in GridView (using LLBLGenDataSource2 as GridView's datasource).

I'm using the latest version of LLBLGen.

        protected void Page_Load(object sender, EventArgs e)
        {
            EntityCollection<BookEntity> orders = new EntityCollection<BookEntity>(new BookEntityFactory());
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.BookEntity);
            prefetchPath.Add(BookEntity.PrefetchPathBookType);
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(BookFields.Title == "Winter");
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(orders, filter, prefetchPath);

            myDS.FilterToUse = filter;
            myDS.AdapterToUse = adapter;
        }

The last column called BookTypeId is a guid value. I have BookTypeEntity which has Id that represents BookTypeId from BookEntity.

So:

Table Book

Title ..... BookTypeId

Table BookType

Id Name

I would like gridview not to display Book.BookTypeId, but to display BookType.Name where Book.BookTypeId == BookType.Id. ( m:1 relation beetwene Book and BookTypeId using FK_Book_BookType) sorting works for all columns, so i would like sorting to work for this related column "Name" as well.

I've been reading threads on this forum for the last couple of days, but I couldn't get this to work.

How can I update the code above to get this to work?

Thanks!

Please follow the forum rules: create a new thread, refer to this one and explain your problem in details.

Also look at the manual, as it has enough code examples for this particular issue.