title: Entity Framework Select Statement with Logic
body: <p>Faced with what feels like a minor challenge. I am attempting to retrieve a collection of objects as type &lt; ServicesViewModel > from a two different models of type &lt; Services > and &lt; BlacklistedServices  >.</p>
  
  <p>First: Type &lt; Services >, call it Services. (Shown in 2nd code sample)</p>
  
  <p>Second: type &lt; BlacklistedServices  >, call it BlacklistedServices .</p>
  
  <pre><code>Dim BlacklistedServices As IQueryable(Of Models.BlacklistedServices ) = unitOfWork.AutoServiceBlackList.GetAll()
  </code></pre>
  
  <p>The relation between these two collections are as follows. Certain Services that are blacklisted exist as a record in BlacklistedServices.</p>
  
  <p>The following is what I am trying to achieve:</p>
  
  <ol>
  <li>Create a collection of all Services and:
  <ol>
  <li>If a record for a service exists in BlacklistedServices, then set a respective property "Blacklisted " to True.</li>
  <li>If a record for a service exists in BlacklistedServices, then determine what that respective blacklistedservice record's "Enforced" property is set to and set a respective property "Enforced " to what ever the value is.</li>
  </ol></li>
  </ol>
  
  <p>I have most of this already done. I am stuck on on part (1.2).</p>
  
  <p>The following is my working code:</p>
  
  <pre><code>    Services= uow.Services.GetAll().Select(Function(service) New ServiceViewModel() With
      {.ServiceID = service.ServiceID,
      .PropertyABC= service.ABC,
      .PropertyDEF= service.DEF.
      .Blacklisted = BlacklistedServices.Any(Function(BLS) BLS.ServiceName = service.Name),
      .Enforced = If(BlacklistedServices.Any(Function(BLS) BLS.ServiceName = service.Name),
            BlacklistedServices.FirstOrDefault(Function(BLS) BLS.ServiceName service.Name).Enforced, False)}).ToList()
  </code></pre>
  
  <p>It works perfectly except for when trying to set the .Enforced property on the viewmodel.</p>
  
  <pre><code>.Enforced = If(BlacklistedServices.Any(Function(BLS) BLS.ServiceName = service.Name),
            BlacklistedServices.FirstOrDefault(Function(BLS) BLS.ServiceName service.Name).Enforced, False)}).ToList()
  </code></pre>
  
  <p>The error returned is the following:</p>
  
  <blockquote>
    <p>System.Data.EntityCommandCompliationException: {"An error occurred
    while preparing the command definition. See the inner exception for
    details."} InnerException:{"Unable to cast object of type
    'MySql.Data.Entity.SelectStatement' to type
    'MySql.Data.Entity.LiteralFragment'."}</p>
  </blockquote>
  
  <p>The problem seems to be when attempting to using the logic (an If). It most cases I don't think this would be an issue when constructing a new object and determine what to pass in as parameters; however this is more or less being converted into an IQueryable expression tree that has to be sent to the server. So maybe Entity Framework cannot convert the logic into the SQL logic?</p>
  
  <p>As always, any and all help is appreciated!</p>