Returning a single column

Hello,

Consider having two entities, namely "Application" and "SecurityToken". Application has an "IsSystem" property of type bool and SecurityToken has a "Token" of type "string". An application can have many security tokens, but a security token can only have one and only one application. Given a token, I want to the value of the "IsSystem" of the application of the security token.

Here is what I have:

var query = new Query(typeof(Application));
var innerQuery = new Query(typeof(SecurityToken), Entity.Attribute("Token") == token);
innerQuery.Projection.Add("ApplicationId");
query.Join = Join.Inner(typeof(Application), innerQuery, "Id", "ApplicationId");
query.Projection.Add("IsSystemDefined");
var entity = UnitOfWork.FindOne<Application>(query);
if (entity == null)
{
    throw new ResourceNotFoundException();
}
return entity.IsSystemDefined;

The SQL looks something like this:

SELECT
  t0.Id AS "t0.Id",
  t0.ConsumerKey AS "t0.ConsumerKey",
  t0.ConsumerSecret AS "t0.ConsumerSecret",
  t0.CreatedOn AS "t0.CreatedOn",
  t0.Description AS "t0.Description",
  t0.IsSystem AS "t0.IsSystem",
  t0.LockVersion AS "t0.LockVersion",
  t0.Name AS "t0.Name",
  t0.PartyId AS "t0.PartyId",
  t0.UpdatedOn AS "t0.UpdatedOn",
  t0.ValidFrom AS "t0.ValidFrom",
  t0.ValidTo AS "t0.ValidTo",
  t0.VerificationCodeFormat AS "t0.VerificationCodeFormat",
  t0.VerificationCodeLength AS "t0.VerificationCodeLength"
FROM
  Applications t0
INNER JOIN
  (SELECT
    SecurityTokens.ApplicationId
  FROM
    SecurityTokens
  WHERE
    ((SecurityTokens.Token = 'blablablabla') AND (SecurityTokens.ValidFrom < '1/26/2012 00:18:02' AND SecurityTokens.ValidTo > '1/26/2012 00:18:02'))
  ) t1
ON
  t0.Id = t1.ApplicationId
WHERE
  (t0.ValidFrom < '1/26/2012 00:18:02' AND t0.ValidTo > '1/26/2012 00:18:02')

How do I change the query so that only a single column is returned, such as:

SELECT
  t0.Id AS "t0.Id",
  t0.IsSystem AS "t0.IsSystem",
FROM
  Applications t0
INNER JOIN
  (SELECT
    SecurityTokens.ApplicationId
  FROM
    SecurityTokens
  WHERE
    ((SecurityTokens.Token = 'blablablabla') AND (SecurityTokens.ValidFrom < '1/26/2012 00:18:02' AND SecurityTokens.ValidTo > '1/26/2012 00:18:02'))
  ) t1
ON
  t0.Id = t1.ApplicationId
WHERE
  (t0.ValidFrom < '1/26/2012 00:18:02' AND t0.ValidTo > '1/26/2012 00:18:02')

The aim here is to prevent unnecessary data, such a blob etc from being loaded from the database, and thus reduce memory requirements and improve speed. I have several cases where performance degrades a bit, because of all the data being returned.

Thanks, Werner

FindOne always returns an entity (and therefore ignores the Projection collection). You need to use the Project method.

Furthermore, you do not need to set up all the joins explicitly. You can just use a dotted path in the projection.

Query query = new Query(typeof(SecurityToken), Entity.Attribute("Token") == someToken);
query.Projection.Add("Application.IsSystemDefined");
var results = _unitOfWork.Project<bool>(query);  // assuming IsSystemDefined is a boolean
if (results.Count != 1)
  throw new ResourceNotFoundException();
return results[0];

LightSpeed will create the required join for you, and will select only the single column specified in the Projection collection.

That is pretty sweet. Thanks.