Custom Find, Search and/or Order Methods and why it is important to maintain the domain <-> Table architecture
There will be many situations where you need custom searches based on multiple parameters and/or custom ordering. While you could apply LINQ/LAMBDA expressions against the Table.GetAll() method, but this won't be CPU or disk efficient, because all the rows from the table would need to be loaded into memory for the LINQ to be processed.
A better way, is to implement your own Search algorithms. You implement these in your own partial class implementations for the table and domain. This absolutely required because the system will overwrite any custom code if you update the files that are generated.
When SQLINQ generates your code, it generates it as a "partial" class. The linker can then merge several "partial" classes together to form a full class. For example, the SettlementTable is defined as:
namespace Assets.Data.Tables {
public partial class SettlementTable : IBaseTable { }
}
For you to implement extensions to this class you create a new C# file (recommended) in a different folder, and create this same specification, therefore, to implement a search algorithm that is different than what is generated, you might say:
This is done by executing LINQ directly against a SQL.Net Table, such as:
namespace Assets.Data.Tables {
public partial class SettlementTable : IBaseTable {
public static IEnumerable<SettlementTable> GetByPage(int aPage, int aPageSize ) {
return Repository.GetTemporaryDatabaseConnection().Table<SettlementTable>()
.Skip((aPage-1)*aPageSize).Take(aPageSize);
}
}
}
Then you define your custom class as:
namespace Assets.Data.DataDomain {
public partial class Settlement : BaseClass {
public static IEnumerable<Settlement> GetByPage( int aPage, int aPageSize = 20 ) {
return Repository.Factory<SettlementTable,Settlement>( SettlementTable.FindByNamePage( aPage, aPageSize ) );
}
}
}
Recommendation
Technically, you could implement the Table commands directly in the Domain Class, but it's better to implement a layered approach because it reduces complexity.
return Repository.Factory<SettlementTable,Settlement>( SettlementTable.FindByNamePage( aPage, aPageSize ) );
is easier to read than:
return Repository.Factory<SettlementTable,Settlement>( Repository.GetTemporaryDatabaseConnection().Table<SettlementTable>() .Skip((aPage-1)*aPageSize).Take(aPageSize) );
Important Note about this Recommendation
It's more than just about implementing a layered approach. You will need a separation point between what is considered "domain" and what are table/database instances. For example, suppose your original implementation of the SettlementTable was to extract data from the database, but decide later it would be more efficient to store it as XML (for whatever reason). In a layered approach, the domain might think that it's interacting with a table, but in reality it's interacting with an XML implementation. By separating "how" from the "what", you make it easier to change "how" data is loaded and saved.
If you implemented the second approach throughout your code, by having these monster calls, you now have to replace every Table<SettlementTable> call in order to support that notion. It's far easier to implement this kind of change if the way in which persistency is managed is contained within a single class because you know exactly the interface the behaviour is expecting to provide. Moving the way from this central idea creates, coupling, which code that is much more difficult to maintain and often results in shortcuts which leads to bugs and higher maintenance/development costs.
A better way, is to implement your own Search algorithms. You implement these in your own partial class implementations for the table and domain. This absolutely required because the system will overwrite any custom code if you update the files that are generated.
When SQLINQ generates your code, it generates it as a "partial" class. The linker can then merge several "partial" classes together to form a full class. For example, the SettlementTable is defined as:
namespace Assets.Data.Tables {
public partial class SettlementTable : IBaseTable { }
}
For you to implement extensions to this class you create a new C# file (recommended) in a different folder, and create this same specification, therefore, to implement a search algorithm that is different than what is generated, you might say:
This is done by executing LINQ directly against a SQL.Net Table, such as:
namespace Assets.Data.Tables {
public partial class SettlementTable : IBaseTable {
public static IEnumerable<SettlementTable> GetByPage(int aPage, int aPageSize ) {
return Repository.GetTemporaryDatabaseConnection().Table<SettlementTable>()
.Skip((aPage-1)*aPageSize).Take(aPageSize);
}
}
}
Then you define your custom class as:
namespace Assets.Data.DataDomain {
public partial class Settlement : BaseClass {
public static IEnumerable<Settlement> GetByPage( int aPage, int aPageSize = 20 ) {
return Repository.Factory<SettlementTable,Settlement>( SettlementTable.FindByNamePage( aPage, aPageSize ) );
}
}
}
Recommendation
Technically, you could implement the Table commands directly in the Domain Class, but it's better to implement a layered approach because it reduces complexity.
return Repository.Factory<SettlementTable,Settlement>( SettlementTable.FindByNamePage( aPage, aPageSize ) );
is easier to read than:
return Repository.Factory<SettlementTable,Settlement>( Repository.GetTemporaryDatabaseConnection().Table<SettlementTable>() .Skip((aPage-1)*aPageSize).Take(aPageSize) );
Important Note about this Recommendation
It's more than just about implementing a layered approach. You will need a separation point between what is considered "domain" and what are table/database instances. For example, suppose your original implementation of the SettlementTable was to extract data from the database, but decide later it would be more efficient to store it as XML (for whatever reason). In a layered approach, the domain might think that it's interacting with a table, but in reality it's interacting with an XML implementation. By separating "how" from the "what", you make it easier to change "how" data is loaded and saved.
If you implemented the second approach throughout your code, by having these monster calls, you now have to replace every Table<SettlementTable> call in order to support that notion. It's far easier to implement this kind of change if the way in which persistency is managed is contained within a single class because you know exactly the interface the behaviour is expecting to provide. Moving the way from this central idea creates, coupling, which code that is much more difficult to maintain and often results in shortcuts which leads to bugs and higher maintenance/development costs.