LLBLGen Pro exception: The multi-part identifier "MyDatabase.dbo.User.Name" could not be bound

If you get an exception like:

System.Web.HttpUnhandledException (0x80004005): Exception of type ‘System.Web.HttpUnhandledException’ was thrown. —> SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "MyDatabase.dbo.User.Name" could not be bound..

make sure you use corresponding EntityFactory and Fields:

 

EntityCollection items = new EntityCollection(new UserEntityFactory()); PredicateExpression filter = new PredicateExpression(Userields.Name == username); IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.PredicateExpression.AddWithAnd(filter); using (var adapter = ConnectionHelper.GetAdapter(Source.LocatiePlatform, _logger)) {     adapter.FetchEntityCollection(items, bucket);     adapter.CloseConnection(); }

 

 

In the code above if you change the UserEntityFactory to an other “table factory” like PersonEntityFactory, you will get the 0x80004005 exception.

Update a many to many relationship with LLBLGen Pro and a ASP .NET DetailsView

If you have a database model like:

image

and you want to edit a CustomerEntity with LLGLGen Pro, before you save the CustomerEntity, you should delete all records in the table CustomerPermission.

 

 

Update a Customer

The code is from a ASP .NET Webpage that uses a DetailsView to edit a Customer. It uses a CheckBoxList in the DetailsView to add and delete references between the table Customer and the table Permission.

public void Update_Click(object sender, DetailsViewUpdateEventArgs e) { // Get the current CustomerId from the current URL string customerIdInQueryString = Request.QueryString["CustomerId"]; if (!string.IsNullOrEmpty(customerIdInQueryString)) { // Only save or create a customer when a CustomerId is supplied in the URL int customerId = 0; if (int.TryParse(customerIdInQueryString, out customerId)) { CustomerEntity customer = new CustomerEntity(); // If customerId == -1 create a new customer, else edit an existing customer if (customerId == -1) { customer.IsNew = true; } else { customer.IsNew = false; } // Set customer properties customer.Id = customerId; customer .Name = e.NewValues["Name"] == null ? null : e.NewValues["Naam"].ToString();

// Foreach item in the CheckBoxList that is selected create a reference from Customer to Permission CheckBoxList cbl = (CheckBoxList)this.customerDetailsView.FindControl("PermissionCheckBoxList"); customer.CustomerPermission.Clear(); foreach (ListItem li in cbl.Items) { if (li.Selected) { CustomerPermissionEntity spe = new CustomerPermissionEntity() { CustomerId = customer.Id, PermissionId = int.Parse(li.Value) }; customer.CustomerPermission.Add(spe); } } this.UpdateCustomer(customer); // Go back to overview page (containing a RadGrid) Response.Redirect("CustomerOverview.aspx"); } } }

private void UpdateCustomer(Customerntity customer)
{
using (var adapter = new DataAccessAdapter(ConfigurationManager.ConnectionStrings["Main.ConnectionString"].ConnectionString))
{
// Delete entries in table CustomerPermission
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(CustomerPermissionFields.CustomerId == customer.Id);
adapter.DeleteEntitiesDirectly(typeof(CustomePermissionEntity), bucket);
// Save Customer
adapter.SaveEntity(customer, true, true);
adapter.CloseConnection();
}
}

Exception during SaveEntity with LLBLGen Pro

I wanted to update an entity with LLGLGen Pro by executing the adapter.SaveEntity() method and got an exception.

 

Error

An exception was caught during the execution of an action query: Cannot insert duplicate key row in object ‘dbo.Customer’ with unique index ‘UniqueApi’.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

 

Cause

This was caused by an unique constraint on a column ApiKey, but I was updating an existing “Customer” entity and the value for the “ApiKey”, was unique, but I forgot to set the IsNew property on the “Customer” entity to false. So LLGLGen wanted to insert a new record instead of updating the existing record.

 

Solution

By setting the “IsNew” property on the “Customer” entity to false, the exception was resolved.

Can’t change database name during catalog refresh in LLBLGen Pro 2.6

If you change your database name and then open LLBLGen Pro 2.6, you can’t change you’re database name. If you choose the new database, the Project Explorer will show 2 database Catalogs. So if you change you’re database name, rename you’re catalog name first, before you refresh you’re catalog.

Change the database name (catalog name) can be done by using the Catalog Explorer, it can be found on the right of the screen.

  image

Cannot find column Customer when sorting a Telerik RadGrid column with LLBLGen entities

When you sort a column in a Telerik RadGrid you get the error “Cannot find column Customer”, make sure the  SortExpression is the same as the DataField

                   DataField="Customer.Description"
HeaderText="Customer"
SortExpression="Customer.Description"
UniqueName="Customer" 


If SortExpression is not the same as the DataFiel, you will get the error:

Message: Cannot find column Customer

Exception: System.IndexOutOfRangeException

Targetsite: System.Data.IndexField[] ParseSortString(System.String)

Source: System.Data

StackTrace: at System.Data.DataTable.ParseSortString(String sortString)

at System.Data.DataView.CheckSort(String sort)

at System.Data.DataView.set_Sort(String value)

at Telerik.Web.UI.GridEnumerableFromDataView.PerformTransformation()

at Telerik.Web.UI.GridEnumerableFromDataView.TransformEnumerable()

at Telerik.Web.UI.GridTableView.GetEnumerator(Boolean useDataSource, GridEnumerableBase resolvedDataSource, ArrayList dataKeysArray)

at Telerik.Web.UI.GridTableView.CreateControlHierarchy(Boolean useDataSource)

at Telerik.Web.UI.GridTableView.CreateChildControls(IEnumerable dataSource, Boolean useDataSource)

at System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data)

at System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data)

at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback)

at System.Web.UI.WebControls.DataBoundControl.PerformSelect()

at Telerik.Web.UI.GridTableView.PerformSelect()

at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()

at Telerik.Web.UI.GridTableView.DataBind()

at Telerik.Web.UI.GridSortCommandEventArgs.ExecuteCommand(Object source)

at Telerik.Web.UI.RadGrid.OnBubbleEvent(Object source, EventArgs e)

at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)

at Telerik.Web.UI.GridItem.OnBubbleEvent(Object source, EventArgs e)

at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)

at Telerik.Web.UI.GridItem.OnBubbleEvent(Object source, EventArgs e)

at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)

at System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e)

at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)

at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)

at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)

at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Data count: 0

Delete all records from a table with LLBLGen Pro v2.6

If you want to delete all records from a table with LLBLGen Pro v2.6 without first fetching all records, you can use the DataAccessAdapter DeleteEntitiesDirectly function.

            // Define the query that matches all records to be deleted.
// If no PredicateExpression are added all records will be deleted.
RelationPredicateBucket bucket = new RelationPredicateBucket(); // If you want to delete only the customers that have an Id != 100, uncomment the following line // bucket.PredicateExpression.Add(CustomerFields.Id != 100); // Initialize DataAccessAdapter using (DataAccessAdapter da = new DataAccessAdapter("server=MyServer; database=Test; Trusted_Connection=False;")) { // Delete the records from table CustomerEntity (TSQL query: delete Customer) da.DeleteEntitiesDirectly("CustomerEntity", bucket); }

How to use datediff with LLBLGen Pro

If you want to use datedif to filter on a date, you can use code like:

        public EntityCollection<Customers> GetCustomers(DateTime creationDate)
{
EntityCollection<AfhaalopdrachtEntity> result = new EntityCollection<AfhaalopdrachtEntity>();
// Filter
IRelationPredicateBucket filter = new RelationPredicateBucket();
IPredicate customerCreateDateFilter = new EntityField2("CreateCustomerDiff", new DbFunctionCall("DATEDIFF(day, {0}, {1})", new object[] { CustomerFields.CreationDate, creationDate})) == 0;
filter.PredicateExpression.Add(customerCreateDateFilter );
// Fetch (get the data)
using (DataAccessAdapter da = ConnectionHelper.GetAdapter(Source.Pegaso, Global.Logger))
{
da.FetchEntityCollection(result, filter);
}
return result;
}

Using TSQL Like operator in LLBLGen

If you want to query you’re database with LLBLGen, by filtering entities using the TQL Like operator you should use the FieldLikePredicate

See LLGLGen v2.6 documentation: http://www.llblgen.com/documentation/2.6/hh_start.htm
Specific part: http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/Adapter/Filtering%20and%20Sorting/gencode_filteringpredicateclasses_adapter.htm#FieldLikePredicate

FieldLikePredicate

Description
compares the entity field specified with the pattern specified, using the LIKE operator. The pattern should contain the wildcard, which is ‘%’ (also for MS Access). FieldLikePredicate performs a LIKE compare using the case sensitivity setting of the database system the query is executed on: the SQL generated does not contain any collation information nor any case insensitive setting if the database is using case sensitive comparison operations by default (Oracle, some SqlServer installations). You can perform case insensitive compares however, if the database is case sensitive, by setting the CaseSensitiveCollation property to true prior to passing the predicate to a fetch method like FetchEntityCollection(). This will perform the UPPERCASE variant of the field with the pattern specified.Please note that if you’ve set CaseSensitiveCollaction to true, you’ve to specify your pattern in uppercase as well.

SQL equivalent examples
Field LIKE ‘%bla’
Field LIKE ‘bla%’

Operators
none.

Example
This example creates a predicate which compares Customer.CompanyName to the pattern "Solution%".

  • C#
  • VB.NET
// C#
filter.Add(new FieldLikePredicate(CustomerFields.CompanyName, null, "Solution%"));
// Which is equal to:
filter.Add(CustomerFields.CompanyName % "Solution%");
' VB.NET
filter.Add(New FieldLikePredicate(CustomerFields.CompanyName, Nothing, "Solution%"))
' Which is equal to: (VB.NET 2005)
filter.Add(CustomerFields.CompanyName Mod "Solution%")

Note, that the operator syntaxis is a little odd in VB.NET, due to the fact that there isn’t an ability to add new operators to VB.NET/C#.

Can be used for in-memory filtering

Yes. When used in in-memory filters, the pattern can either be a normal LIKE statement pattern with ‘%’ wildcards, or it can be a full regular expression. If the pattern is a regular expression, be sure to set the property PatternIsRegEx to true. See also the LLBLGen Pro reference manual on more detailed information about the properties of the FieldLikePredicate

Sort ASP .NET Gridview on related table (prefetchpath) column with LLBLGen Pro

If you have a table Order and a related table Customer, you can sort and filter this data for ASP .NET with LLBLGen pro, as mentioned in the LLBLGen Pro documentation:

// Define result entitycollection
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();

// Define sort (sort result on column "Order.ShipCountry")
SortExpression result = new SortExpression();
result.Add(CustomerFields.Name | SortOperator.Ascending);
result[0].CaseSensitiveCollation = false;

// Define filter by adding relations (= inner join in tsql)
RelationPredicateBucket customerFilter = new RelationPredicateBucket();
customerFilter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);

// Define filter by adding predicateexpression (= where in tsql)
customerFilter.PredicateExpression.Add(OrderFields.ShipCountry=="Brazil");

// Load for all customers fetched their orders.
PrefetchPath2 path = new PrefetchPath2(EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders);

// Get the data
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
     adapter.FetchEntityCollection(customers, customerFilter, path);
}

If you forget to add the relations, you might get the exception:

An exception was caught during the execution of a retrieval query: The multi-part identifier "dbo.Customer.Name" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

This is because there is no inner join on Customer, so Customer.Name does not exist in the query

Error on calling stored procedure with LLBLGen Pro {"The ConnectionString property has not been initialized."}

When you get the error {“The ConnectionString property has not been initialized.”} on calling a stored procedure with LLBLGen Pro, you probably forgot to set the parameter “DataAccessAdapter adapter” on you’re LLBLGen Pro methode call:

RetrievalProcedures.MyStoreProcedure(param1, param2, dataAccessAdapter);

if you call the function like RetrievalProcedures.MyStoreProcedure(param1, param2); you will get the error {“The ConnectionString property has not been initialized.”}