title: Binding Complex Model Entity With Raw SQL Query?
body: <p>I have been using entity framework quite some and I can do complex model binding with entity itself easily, but when it comes to raw sql binding I cant find any solutions that is doing complex binding of relation db.</p>
  
  <p>for example following is a entity to model code for product class- </p>
  
  <pre><code>var prodResult = db.products.Where(p =&gt; p.isActive == true).Select(p =&gt; new ComplexProductModel
                  {
                      id = p.id,
                      dateCreated = p.date_created,
                      datePublished = null,
                      desc = p.product_desc,
                      images = db.products_images.Where(pi =&gt; pi.prod_id == p.id).Select(pi =&gt; pi.prod_img_thumb).ToList(),
                      summary = p.product_summary,
                      title = p.product_name + " " + p.product_code
                  }).ToList();
  </code></pre>
  
  <p>This realizes data against complex Model holding a list type of string or class inside main Complex Model as per query requirements.</p>
  
  <p>Now I tried running same generating query with raw sql execution, but the binding for images object is failing.</p>
  
  <pre><code>var rawComplexData = db.Database.SqlQuery&lt;ComplexProductModel&gt;(dynamicSearchQuery).ToList(); 
  </code></pre>
  
  <p>Is there any solution for this situation, what I am getting is 10 rows of data(for this example) with images null which should be 7 rows with duplicate fetched images going into <code>List&lt;string&gt; images</code> for the <code>ComplexProductModel</code>.</p>
  
  <p>Here is the complex product model for better understanding the code:</p>
  
  <pre><code>public ComplexProductModel {
          long? id { get; set; }
          string title { get; set; }
          string desc { get; set; }
          string summary { get; set; }
          List&lt;string&gt; images { get; set; }
          DateTime? datePublished { get; set; }
          DateTime? dateCreated { get; set; }
      }
  </code></pre>
  
  <p>As per @tchelidze request here is <code>dynamicQuery</code> which is basically what i grabbed from what entity framework was generating itself :</p>
  
  <pre><code>SELECT 
      [Project2].[C1] AS [C1], 
      [Project2].[id] AS [id], 
      [Project2].[date_created] AS [date_created], 
      [Project2].[C2] AS [C2], 
      [Project2].[product_desc] AS [product_desc], 
      [Project2].[product_summary] AS [product_summary], 
      [Project2].[C3] AS [C3], 
      [Project2].[C4] AS [C4], 
      [Project2].[prod_img_thumb] AS [prod_img_thumb]
      FROM ( SELECT 
          [Extent1].[id] AS [id], 
          [Extent1].[product_desc] AS [product_desc], 
          [Extent1].[product_summary] AS [product_summary], 
          [Extent1].[date_created] AS [date_created], 
          1 AS [C1], 
          CAST(NULL AS datetime2) AS [C2], 
          [Project1].[prod_img_thumb] AS [prod_img_thumb], 
          CASE WHEN ([Extent1].[product_name] IS NULL) THEN N'' ELSE [Extent1].[product_name] END + N' ' + CASE WHEN ([Extent1].[product_code] IS NULL) THEN N'' ELSE [Extent1].[product_code] END AS [C3], 
          [Project1].[C1] AS [C4]
          FROM  [dbo].[products] AS [Extent1]
          LEFT OUTER JOIN  (SELECT 
              [Extent2].[prod_id] AS [prod_id], 
              [Extent2].[prod_img_thumb] AS [prod_img_thumb], 
              1 AS [C1]
              FROM [dbo].[products_images] AS [Extent2] ) AS [Project1] ON [Project1].[prod_id] = [Extent1].[id]
          WHERE 1 = [Extent1].[isActive]
      )  AS [Project2]
      ORDER BY [Project2].[id] ASC, [Project2].[C4] ASC
  </code></pre>