public void DetachesOwnedBeans()
{
_api.Exec("CREATE TABLE Bean1 (id, Prop)");
_api.Exec("INSERT INTO Bean1 VALUES(1, 'Bean1Prop')");
_api.Exec("CREATE TABLE Bean4 (id, Bean1_id, Prop)");
_api.Exec("INSERT INTO Bean4 VALUES (1, 1, 'Bean4Prop1')");
_api.Exec("INSERT INTO Bean4 VALUES (2, 1, 'Bean4Prop2')");
_api.Exec("INSERT INTO Bean4 VALUES (3, 1, 'Bean4Prop3')");
_api.Exec("INSERT INTO Bean4 VALUES (4, 1, 'Bean4Prop4')");
var bean1 = _api.Load("Bean1", 1);
var beanList = bean1.GetOwnedList("Bean4").ToList();
var detachDelBeans = beanList.Where(b => b.Get <Int64>("id") % 2 == 0).ToList();
// Delete related
Assert.True(bean1.DetachOwned(detachDelBeans, true));
beanList = bean1.GetOwnedList("Bean4").ToList();
Assert.Equal(2, beanList.Count);
Assert.Equal(1L, beanList[0]["id"]);
Assert.Equal(3L, beanList[1]["id"]);
// Detach related
Assert.True(bean1.DetachOwned(beanList));
beanList = bean1.GetOwnedList("Bean4").ToList();
Assert.Empty(beanList);
var orphanedBeanList = _api.Find("Bean4", "WHERE Bean1_id IS NULL");
Assert.Equal(2, orphanedBeanList.Length);
Assert.Equal("Bean4Prop1", orphanedBeanList[0]["Prop"]);
Assert.Equal("Bean4Prop3", orphanedBeanList[1]["Prop"]);
}
void FindingBeansWithSql(BeanApi api)
{
/// ## Finding Beans with SQL
/// LimeBean doesn't introduce any custom query language, nor does it implement a LINQ provider.
/// To find beans matching a criteria, use fragments of plain SQL:
///
{
#if CODE
var list = api.Find("book", "WHERE rating > 7");
#endif
}
/// Instead of embedding values into SQL code, it is recommended to use **parameters**:
{
#if CODE
var list = api.Find("book", "WHERE rating > {0}", 7);
#endif
}
/// Usage of parameters looks similar to `String.Format`, but instead of direct interpolation,
/// they are transformed into fair ADO.NET command parameters to protect your queries from SQL-injection attacks.
///
{
#if CODE
var list = api.Find(
"book",
"WHERE release_date BETWEEN {0} and {1} AND author LIKE {2}",
new DateTime(1930, 1, 1), new DateTime(1950, 1, 1), "%remarque%"
);
#endif
}
///
/// You can use any SQL as long as the result maps to a set of beans.
/// For other cases, see [Generic Queries](#generic-sql-queries).
///
/// To find a single bean:
#if CODE
var best = api.FindOne("book", "ORDER BY rating DESC LIMIT 1");
#endif
/// To find out the number of beans without loading them:
#if CODE
var count = api.Count("book", "WHERE rating > {0}", 7);
#endif
/// It is also possible to perform unbuffered (memory-optimized) load for processing in a `foreach` loop.
///
/// Data is 'Lazy Loaded' on each iteration using [C-sharp's IEnumerable Yield](http://programmers.stackexchange.com/a/97350)
#if CODE
foreach (var bean in api.FindIterator("book", "ORDER BY rating"))
{
// do something with bean
}
#endif
}
/// Doing so has several advantages:
///
/// - All strings prone to typos (bean kind and field names) are encapsulated inside.
/// - You get compile-time checks, IDE assistance and [typed properties](#typed-accessors).
/// - With [Lifecycle Hooks](#lifecycle-hooks), it is easy to implement [data validation](#data-validation) and [relations](#relations).
///
/// For [Custom Beans Classes](#custom-bean-classes), use method overloads with a generic parameter:
///
void Overloads(BeanApi api)
{
#if CODE
api.Dispense <Book>();
api.Load <Book>(1);
api.Find <Book>("WHERE rating > {0}", 7);
// and so on
#endif
}
public IActionResult Index()
{
ViewBag.Books = _beans.Find("book", "ORDER BY title");
return(new ViewResult());
}
/// <summary>
/// Gets a List of Beans that are owned by the current Bean in a
/// manner of a 1:n reference.
/// If the Foreign Key Name of the (owned) Bean differs form the
/// Name of the Owner Bean, it is possible to pass an alias name.
/// </summary>
/// <param name="ownedKind">Kind of Bean that references to this Bean via Foreign Key.</param>
/// <param name="fkAlias">Alias for the Owner Bean's Foreign Key Name (w/o Primary Key Name)</param>
/// <returns>List of owned Beans.</returns>
public IList <Bean> GetOwnedList(string ownedKind, string fkAlias = "")
{
return(Api.Find(ownedKind, "WHERE " + GetFkName(fkAlias == string.Empty ? GetKind() : fkAlias) +
" = {0}", GetKeyValue()).ToList());
}
/// Doing so has several advantages:
///
/// - All strings prone to typos (bean kind and field names) are encapsulated inside.
/// - You get compile-time checks, IDE assistance and [typed properties](#typed-accessors).
/// - With [Lifecycle Hooks](#lifecycle-hooks), it is easy to implement [data validation](#data-validation) and [relations](#relations).
///
/// For [Custom Beans Classes](#custom-bean-classes), use method overloads with a generic parameter:
///
void Overloads(BeanApi api)
{
#if CODE
api.Dispense<Book>();
api.Load<Book>(1);
api.Find<Book>("WHERE rating > {0}", 7);
// and so on
#endif
}
void FindingBeansWithSql(BeanApi api)
{
/// ## Finding Beans with SQL
/// LimeBean doesn't introduce any custom query language, nor does it implement a LINQ provider.
/// To find beans matching a criteria, use snippets of plain SQL:
///
{
#if CODE
var list = api.Find("book", "WHERE rating > 7");
#endif
}
/// Instead of embedding values into SQL code, it is recommended to use **parameters**:
{
#if CODE
var list = api.Find("book", "WHERE rating > {0}", 7);
#endif
}
/// Usage of parameters looks similar to `String.Format`, but instead of direct interpolation,
/// they are transformed into fair ADO.NET command parameters to protect your queries from injection-attacks.
///
{
#if CODE
var list = api.Find(
"book",
"WHERE release_date BETWEEN {0} and {1} AND author LIKE {2}",
new DateTime(1930, 1, 1), new DateTime(1950, 1, 1), "%remarque%"
);
#endif
}
///
/// You can use any SQL as long as the result maps to a set of beans.
/// For other cases, see [Generic Queries](#generic-sql-queries).
///
/// To find a single bean:
#if CODE
var best = api.FindOne("book", "ORDER BY rating DESC LIMIT 1");
#endif
/// To find out the number of beans without loading them:
#if CODE
var count = api.Count("book", "WHERE rating > {0}", 7);
#endif
/// It is also possible to perform unbuffered (memory-optimized) load for processing in a foreach-loop:
#if CODE
foreach(var bean in api.FindIterator("book", "ORDER BY rating")) {
// do something with bean
}
#endif
}
