3

What I am trying to do boils down to trying concatenate multiple rows into a single row from a column subquery. In the SQL in MySql this is done with a GROUP_CONCAT function.

I have seen code examples like this that should work:

var qryProd = from prod in db.Products
              group word by prod.category into q
              select new { prod.category,  products = string.Join(',', q.Select(p => p.name)) } ;

I am attempting to run this as a small part in a larger query, and as a Column SubQuery, code looks something like this:

var qry = from cat in db.Category
          select new { 
                 cat.category,
                 products = (from word in db.Products
                             where word.category == cat.category
                             group word by word.category into temp
                             select string.Join(',', temp.Select(p => p.name)))
                 };

I am getting this error:

KeySelector: p.category, 
ElementSelector:new { 
    category = ProjectionBindingExpression: category, 
    name = ProjectionBindingExpression: name, 
 }
' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in Entity Framework. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

I doubt there is a bug in the Framework, but wondering if String.Join has not been mapped to the appropriate aggregate function in MySql.

So I was wondering if I would be able to create a custom EF function that could call GROUP_CONCAT directly in the query. This has been a rabbit hole of exploration.

The simplest would have been from: C# call a SQL Server user defined function via Entity Framework in essence here is the method:

public string GetFruit(int customerId)
{
    return Database.SqlQuery<string>("select dbo.fngetfruit(@customerId) fruit", new System.Data.SqlClient.SqlParameter("@customerID", customerId)).Single();
}

The issue is the SqlQuery does not seem to be supported on recent versions of EF, you can run a SQL statement directly off a table set by a DbSet command, but no longer able to run it on a generic type, much-less a data primative.

Then I came across this on how to map an aggregate function more directly into EF: Code first Entity Framework 6.1 Custom Aggregate Function but it seems to be EF 6 centric, and I am currently on 5, and worry about Preview Editions. I tried to implement the code, but one of the classes is not found IStoreModelConvention.

Am I out of luck here, or is there still something I can try here? The only other option I can think to use, and seems to negate most of the reason for using EF is to just map a class to a View that has all the needed logic.

EDIT: The model is quite big, but in essence their are three tables to create a multi-multi relationship:

PRODUCT {
   id varchar(45) Primary Key,
   title varchar(100)
}
KEYWORDS {
   id int Primary Key,
   category varchar(45),
   keyword varchar(45)
}
PRODUCT_KEYWORDS {
   PROD_ID varchar(45),
   KEYWORD_ID int
} // Both columns together compose the Primary Key

I decomposed the query into smaller parts so that it can be easier to put together:

var qryKeywords = from keyword in context.KEYWORDS
    join prodWords in context.PRODUCT_KEYWORDS on keyword.id equals (prodWords.keyword_id)
    select new { prodWords.product_id, keyword.keyword, keyword_id = keyword.id };

var qry = from prod in context.PRODUCTS
    join v1 in context.VENDOR on prod.vendor_id equals (v1.id) into t1
    from vendor in t1.DefaultIfEmpty()
    join b1 in context.BRANDS on prod.brand_id equals (b1.brand_id) into t2
    from brand in t2.DefaultIfEmpty()
    select new {
        product_id = prod.product_id,
        product_title = prod.title,
        brand = brand_name,
        vendor = vendor.name,
        keywords = (from word in qryKeywords
                    where word.product_id == prod.product_id
                    group word by word.product_id into t1
                    select string.Join(',', t1.Select(p => p.keyword))).FirstOrDefault(),
        keyword_ids = (from word in qryKeywords
                    where word.product_id == prod.product_id
                    group word by word.product_id into t1
                    select string.Join(',', t1.Select(p => p.keyword_id))).FirstOrDefault()
    };

The qryKeywords is able to generate a valid SQL statement, if I comment out the fields KEYWORDS an KEYWORD_IDS, the main query is also able to produce a valid SQL statement.

This is an attempt to get something that would look like this in the end:

| product_title | keywords                        | keyword_ids    |
| ------------- | ---------                       | -----------    |
| phone         | easy setup,easy to use,warranty | 1003,2004,1095 |

Also remember this is using the MySql provider "Pomelo" for the system/database call level.

Thank you for any help you can offer. This has a lot going on inside, sub-queries, left joins, and aggregate functions.

user2771416
  • 31
  • 3

0 Answers0