The documentation comes from the Markdown files in the source code, so is always up-to-date but available only in English. Enjoy!
You can mix values, entities, and Lite<T>
in your results when calling Linq Queries.
var result = from b in Database.Query<BugEntity>()
select new { b.Status, b.Fixer, b.Project };
Producing the Sql:
SELECT bdn.idStatus, ddn.Id, ddn.Ticks, ddn.Name, ddn.ToStr, bdn.idFixer, bdn.idProject, pdn.ToStr AS ToStr1
FROM BugEntity AS bdn
LEFT OUTER JOIN DeveloperEntity AS ddn
ON (bdn.idFixer = ddn.Id)
LEFT OUTER JOIN ProjectEntity AS pdn
ON (bdn.idProject = pdn.Id)
idStatus
is enough to retrieve the enum
value.DeveloperEntity
table, in order to retrieve the DeveloperEntity
object.ProjectEntity
table, but just to retrieve the ToStr
column.In this case, retrieving a DeveloperEntity
was just a few columns because is a simple entity but let's try a bigger one like BugEntity
:
var result = from b in Database.Query<BugEntity>()
select b;
Surprisingly, this simpler query creates a much bigger generated SQL:
--------- MAIN QUERY ------------------------
SELECT bdn.Id, bdn.Ticks, bdn.Description, bdn.Start, bdn.[End], bdn.Hours, bdn.idStatus, cdn.Id AS Id1, cdn.Ticks AS Ticks1, cdn.Name, cdn.ToStr, bdn.idDiscoverer_Customer, ddn.Id AS Id2, ddn.Ticks AS Ticks2, ddn.Name AS Name1, ddn.ToStr AS ToStr1, bdn.idDiscoverer_Developer, ddn1.Id AS Id3, ddn1.Ticks AS Ticks3, ddn1.Name AS Name2, ddn1.ToStr AS ToStr2, bdn.idFixer, bdn.idProject, pdn.ToStr AS ToStr3, bdn.ToStr AS ToStr4
FROM BugEntity AS bdn
LEFT OUTER JOIN CustomerEntity AS cdn
ON (bdn.idDiscoverer_Customer = cdn.Id)
LEFT OUTER JOIN DeveloperEntity AS ddn
ON (bdn.idDiscoverer_Developer = ddn.Id)
LEFT OUTER JOIN DeveloperEntity AS ddn1
ON (bdn.idFixer = ddn1.Id)
LEFT OUTER JOIN ProjectEntity AS pdn
ON (bdn.idProject = pdn.Id)
--------- Lazy Client Joins (if needed) -----
SELECT bdn.Id, s3b.Text, s3b.Date, s3b.Id AS Id1, s3b.Ticks, s3b.Name, s3b.ToStr, s3b.idWriter_Customer, s3b.Id1 AS Id11, s3b.Ticks1, s3b.Name1, s3b.ToStr1, s3b.idWriter_Developer, s3b.HasValue, s3b.Id2
FROM BugEntity AS bdn
CROSS APPLY (
(SELECT bdncb.Text, bdncb.Date, cdn1b.Id, cdn1b.Ticks, cdn1b.Name, cdn1b.ToStr, bdncb.idWriter_Customer, ddn2b.Id AS Id1, ddn2b.Ticks AS Ticks1, ddn2b.Name AS Name1, ddn2b.ToStr AS ToStr1, bdncb.idWriter_Developer, bdncb.HasValue, bdncb.Id AS Id2
FROM BugDNComments AS bdncb
LEFT OUTER JOIN CustomerEntity AS cdn1b
ON (bdncb.idWriter_Customer = cdn1b.Id)
LEFT OUTER JOIN DeveloperEntity AS ddn2b
ON (bdncb.idWriter_Developer = ddn2b.Id)
WHERE (bdn.Id = bdncb.idParent))
) AS s3b
The first chunk retrieves the BugEntity
itself, together with the related entities like the Fixer
(a Developer
), the Discoverer
(CustomerEntity
or DeveloperEntity
) and the Project
.
The second chung retrieves all the comments and the writers of each comments (also a CustomerEntity
or DeveloperEntity
).
Take into account that, in the absense of Lite<T>
relationships, the retrieval of the related entities will be eager. This is by design in order to facilitate validation of entities and serialization to other processes (Windows Client), but can cause performance problems if the entities and the queries are not designed carefully.
One important advantage of LINQ over plain SQL is that for most of the joins, those across foreign keys, you don't even have to use a join, you can navigate from one table to the related one just using C# dot (.
) operator. When you try to use a field from an entity in a table that is not in the query, an implicit LEFT OUTER JOIN
is made automatically. Queries become shorter and more readable this way.
//instead of this
var query1 = from b in Database.Query<BugEntity>()
join d in Database.Query<DeveloperEntity>() on b.Fixer.Id equals d.Id
select new { b.Description, d.Name };
//you can write this
var query2 = from b in Database.Query<BugEntity>()
select new { b.Description, b.Fixer.Name };
However, the behavior of both queries is slightly different. We use LEFT OUTER JOIN
for implicit joins because we don't want the number or rows in the results to decrease just because you have used an implicit join somewhere.
SELECT bdn.Description, ddn.Name
FROM BugEntity AS bdn
INNER JOIN DeveloperEntity AS ddn
ON (bdn.idFixer = ddn.Id)
SELECT bdn.Description, ddn.Name
FROM BugEntity AS bdn
LEFT OUTER JOIN DeveloperEntity AS ddn
ON (bdn.idFixer = ddn.Id)
If you need it, one natural way to reduce the results will be:
var result = from b in Database.Query<BugEntity>()
where b.Fixer != null
select new { b.Description, b.Fixer.Name };
That translates to:
SELECT bdn.Description, ddn.Name
FROM BugEntity AS bdn
LEFT OUTER JOIN DeveloperEntity AS ddn
ON (bdn.idFixer = ddn.Id)
WHERE (bdn.idFixer) IS NOT NULL
You can compare two entities using .Is
, ==
operator, or !=
operation, and an automatic identity comparison will be done for you. This is safer than using Ids since you cant get the type wrong.
public IQueryable<BugEntity> DeveloperBugs(DeveloperEntity dev)
{
return Database.Query<BugEntity>().Where(b => b.Id == dev.Id); //BUG HARD TO SPOT!!!!!
return Database.Query<BugEntity>().Where(b => b.Is(dev)); //Bug easy to spot: compile error :), recommended
return Database.Query<BugEntity>().Where(b => b == dev); //Bug easy to spot: compile error :)
return Database.Query<BugEntity>().Where(b => b.Fixer.Is(dev)); //Works!, recommended
return Database.Query<BugEntity>().Where(b => b.Fixer == dev); //Works!
}
Notice how we are using in-memory variables inside of the query, for example the query:
DeveloperEntity dev = new DeveloperEntity { Name = "John" }.Save();
var bug = from b in Database.Query<BugEntity>()
where b.Fixer.Is(dev)
select new { b.Description, b.Hours };
Will translate to:
SELECT bdn.Description, bdn.Hours
FROM BugEntity AS bdn
WHERE (bdn.idFixer = @p1)
@p1 = 2
Note: Notice how the query is automatically parametrized, so you are 100% safe from SQL injection attacks while using Linq to Signum and SQL Server can cache the execution plan.
We have seen that identity comparison works for simple references to entities, but you can also combine it with references of type ImplementedBy
and ImplementedByAll
, so you should use this feature to make the queries simple and future-proof against Schema
changes.
Let's compare the two polymorphic references, Discoverer
and Writer
, each of type Customer
or Developer
:
var discovererComments = from b in Database.Query<BugEntity>()
from c in b.Comments
where b.Discoverer.Is(c.Writer)
select c.Date;
And look how the generated query is smart enough to compare the two implementation columns:
SELECT s1.Date
FROM BugEntity AS bdn
CROSS APPLY (
(SELECT bdnc.Date, bdnc.idWriter_Customer, bdnc.idWriter_Developer
FROM BugDNComments AS bdnc
WHERE (bdn.Id = bdnc.idParent))
) AS s1
WHERE ((bdn.idDiscoverer_Customer = s1.idWriter_Customer) OR (bdn.idDiscoverer_Developer = s1.idWriter_Developer))
There are more complex cases, comparing an arbitrary ImplementedBy
reference against a ImplementedByAll
reference would be pain in the neck if done manually, so use this feature! It saves you problems and you will write safer code.
Entity
overrides Equals
(and GetHashCode
) to compare by Id
and Type
, but does not overload ==
operator.
That means that, in memory, ==
means referential equality (like object.ReferenceEquals
) but object.Equals
return true if two different instances have the same Type an Id.
Unfortunately, calling .Equals
in C# is prone to NullReferenceException
if some object is null, and object.Equals
static method is just too long, so we added Is
extension method.
Note: In order to prevent uninteded referencial equality in entities or lites Signum.Analyzer
now creates a warning when using ==
or !=
to compare entities and lites, encouraging you to use .Is
. In the rare case you want to use referencial equality you can always use object.ReferenceEquals
.
entititB == entititB; //referential equality but Signum.Analyzer produces a warning to prevent unintended use.
object.ReferenceEquals(entititB, entititB); //referential equality, needed in rare cases. No warning produced.
entityA.Equals(entititB); //Type + id equality, but throws NullReferenceException if entityA is null
object.Equals(entityA, entityB); // too long
entityA?.Equals(entityB) == true; // too long
entityA.Is(entititB); //similar to object.Equals but sorter
Both ==
and Is
, Equals
or object.Equals
are supported in the LINQ provider, but only Is
is recommended. The semantics in any of the case are the same, because referential equality makes no sense at the database level.
© Signum Software. All Rights Reserved.
Powered by Signum Framework