none
Union operator and 'data shape' classes (it works in regular select...) RRS feed

  • Question

  • ok, let's say I have a query as such:

    var query =
        (from n in db.NewsItems
         select new
        {
            Prop = n.Prop,
            FullReview = new DataShapeClass()
        });

    public class
    DataShapeClass
    {
        public string Something { get; set; }
    }


    I know this might seem silly to have an empty object set to one of the params but bear with me.The above code compiles fine. But if I do a union I get a 'Could not translate expression' or a 'SQL Server does not handle comparison of NText, Text, Xml, or Image data types' error. For example:

    var query =
        (from n in db.NewsItems
         select new
        {
            Prop = n.Prop,
            FullReview = new DataShapeClass()
        }).Union(
        from n in db.NewsItems
         select new
        {
            Prop = n.Prop,
            FullReview = new DataShapeClass()
        });

    //this does not compile!

    Why doesn't this work? How can I get this (or the basic idea) to work?


    What I'd like to do is concat several queries, the first of which will not have a value for a property that I'd like to use a DataShapeClass for. The class will be instantiated on one of the latter union queries, but since all the params need to be the same it needs to show up in the first queries. Assigning null doesn't seem to work either. Is there a way to do what I'm describing?

    My full query below:

    var query =
        (from n in db.NewsItems
         select new NewsGossipReviewListItem
         {
             ItemType = "News",
             Header = n.Title,
             Username = n.Username,
             Body = n.Body,
             FullReview = new ReivewValuesShortList(),
             DateEntered = n.DateEntered
         })
        .Union(
            from g in db.GossipItems
            select new NewsGossipReviewListItem
            {
                ItemType = "Gossip",
                Header = g.Building.Address + ", " + g.Building.Name,
                Username = g.Username,
                Body = g.Body,
                FullReview = new ReivewValuesShortList(),
                DateEntered = g.DateEntered
            })
        .Union(
            from r in db.BuildingRatings
            join rv in db.BuildingRatingCategoryValues on r.Id equals rv.BuildingRatingId into rvj
            from rv in rvj.DefaultIfEmpty()
            where r.IsFullReview == true
            group rv by new { r.Building.Address, r.Building.Name, r.Username, r.Body, r.DateEntered } into g
            select new NewsGossipReviewListItem
            {
                ItemType = "Review",
                Header = g.Key.Address + ", " + g.Key.Name,
                Username = g.Key.Username,
                Body = g.Key.Body,
                FullReview = new ReivewValuesShortList
                {
                    Overall = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Overall").SingleOrDefault().Rating,
                    Security = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Security").SingleOrDefault().Rating,
                    Ameneties = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Ameneties").SingleOrDefault().Rating,
                    Archicture = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Archicture").SingleOrDefault().Rating,
                    Noise = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Noise").SingleOrDefault().Rating,
                    Management = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Management").SingleOrDefault().Rating,
                    Finishes = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Finishes").SingleOrDefault().Rating,
                    BuildingStaff = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "BuildingStaff").SingleOrDefault().Rating,
                    Neighborhood = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Neighborhood").SingleOrDefault().Rating
                },
                DateEntered = g.Key.DateEntered
            })OrderByDescending(o => o.DateEntered);


    This produces a ' SQL Server does not handle comparison of NText, Text, Xml, or Image data types. ' error. When I take out the FullReview properties is compiles fine.

    Thanks for your assistance,

    bd















    Friday, July 18, 2008 3:28 PM

All replies

  • Not only do the types in the LINQ union operator need to be the same, but SQL needs the set of columns listed in the queries to be identical. The two expresssions "new ReviewValuesShortList()" and "new ReviewValuesShortList() { ... }" generate different sets of columns.

     

    I'm not sure why you are getting that particular error message, though. It is likely the form of "new ReviewValuesShortList()" having no property initializers appears to the translator as a non-scalar type that it cannot break down into separate scalars to project as columns and is falling back on the wrong error message.

     

    The reason you can construct that type when no Union is used is that it is free to become part of the query that is executed locally where it does not need to turn into SQL. When you add the Union operator, it forces the whole query to be computed on the server and unioned together.

     

     

    Still, you might be able to achieve what you want, though with a little more code.  If you create even the empty forms of your type using an object initializer syntax with all the same properties listed in the same order you should be able to get Union to work.

     

    var query =
        (from n in db.NewsItems
         select new NewsGossipReviewListItem
         {
             ItemType = "News",
             Header = n.Title,
             Username = n.Username,
             Body = n.Body,
             FullReview = new ReivewValuesShortList

             {
                    Overall = "",
                    Security = "",
                    Ameneties = "",
                    Archicture = "",
                    Noise = "",
                    Management = "",
                    Finishes = "",
                    BuildingStaff = "",
                    Neighborhood = ""
             },
             DateEntered = n.DateEntered
         })
        .Union(
            from g in db.GossipItems
            select new NewsGossipReviewListItem
            {
                ItemType = "Gossip",
                Header = g.Building.Address + ", " + g.Building.Name,
                Username = g.Username,
                Body = g.Body,
                FullReview = new ReivewValuesShortList

                {
                    Overall = "",
                    Security = "",
                    Ameneties = "",
                    Archicture = "",
                    Noise = "",
                    Management = "",
                    Finishes = "",
                    BuildingStaff = "",
                    Neighborhood = ""
                },
                DateEntered = g.DateEntered
            })
        .Union(
            from r in db.BuildingRatings
            join rv in db.BuildingRatingCategoryValues on r.Id equals rv.BuildingRatingId into rvj
            from rv in rvj.DefaultIfEmpty()
            where r.IsFullReview == true
            group rv by new { r.Building.Address, r.Building.Name, r.Username, r.Body, r.DateEntered } into g
            select new NewsGossipReviewListItem
            {
                ItemType = "Review",
                Header = g.Key.Address + ", " + g.Key.Name,
                Username = g.Key.Username,
                Body = g.Key.Body,
                FullReview = new ReivewValuesShortList
                {
                    Overall = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Overall").SingleOrDefault().Rating,
                    Security = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Security").SingleOrDefault().Rating,
                    Ameneties = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Ameneties").SingleOrDefault().Rating,
                    Archicture = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Archicture").SingleOrDefault().Rating,
                    Noise = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Noise").SingleOrDefault().Rating,
                    Management = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Management").SingleOrDefault().Rating,
                    Finishes = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Finishes").SingleOrDefault().Rating,
                    BuildingStaff = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "BuildingStaff").SingleOrDefault().Rating,
                    Neighborhood = g.Where(rv2 => rv2.BuildingRatingCategory.Name == "Neighborhood").SingleOrDefault().Rating
                },
                DateEntered = g.Key.DateEntered
            })OrderByDescending(o => o.DateEntered);

    Of course, I've just assumed that all the properties were text.

     

     

    Friday, July 18, 2008 4:55 PM
    Moderator
  • I can't seem to get that to work either, even if on both unioned queries I manually assign all the values as the same. For instance this throws an exception:

    var query =
        (from n in db.NewsItems
         select new NewsGossipReviewListItem
         {
             ItemType = "News",
             Header = n.Title,
             Username = n.Username,
             Body = n.Body,
             FullReview = new ReivewValuesShortList
             {
                 Overall = "",
                 Security = "",
                 Ameneties = "",
                 Archicture = "",
                 Noise = "",
                 Management = "",
                 Finishes = "",
                 BuildingStaff = "",
                 Neighborhood = ""
             },
             DateEntered = n.DateEntered
         })
        .Union(
            from g in db.GossipItems
            select new NewsGossipReviewListItem
            {
                ItemType = "Gossip",
                Header = g.Building.Address + ", " + g.Building.Name,
                Username = g.Username,
                Body = g.Body,
                FullReview = new ReivewValuesShortList
                {
                    Overall = "",
                    Security = "",
                    Ameneties = "",
                    Archicture = "",
                    Noise = "",
                    Management = "",
                    Finishes = "",
                    BuildingStaff = "",
                    Neighborhood = ""
                },
                DateEntered = g.DateEntered
            });


    I get the error:

    Could not translate expression 'Table(GossipItem).Select(g => new NewsGossipReviewListItem() {ItemType = "Gossip", Header = ((g.Building.Address + ", ") + g.Building.Name), Username = g.Username, Body = g.Body, FullReview = Invoke(value(System.Func`1[ReivewValuesShortList])), DateEntered = g.DateEntered})' into SQL and could not treat it as a local expression.

    It's definitely the column I'm trying to assign a DataShapeClass (
    ReivewValuesShortList) to that's mucking things up. Is there any other way I can use some sort of multi value data structure for a column value on two sides of a union? Is it possible to use a data shape class in a union in any way?

    bd
    Friday, July 18, 2008 7:25 PM
  • If you bring the results to the client before the union, it will work (I tested that using a simpler query).

     

    However, I do agree with Matt as far as the analysis of the problem goes.

     

    Anyway, here's a solution you could consider: let's say that the vars q1, q2, q3 represent the different queries you'd like to union.

     

    Code Snippet

    var q = (

                 q1.AsEnumerable( )

               ).Union(

                 q2.AsEnumerable( )

              ).Union(

                 q3.AsEnumerable( )

             );

     

     

     

    Hope that helps,

     

    Thanks,

     

    --Samir

     

    Friday, July 18, 2008 11:23 PM
  • Doing it this way, bringing all the data to the client, that would force paging functionality to bring all the results of the query down every time right? Instead of just the page set you needed? With my limited understanding of linq/sql I'm thinking that's the case, and if so, the functionality would perform worse and worse as the data set got bigger.I know this query might seem wonky in and of itself, I think so to, the interface I'm binding the data to is wonky, it's just the way they want it.

    Coming back to the problem though, I'm having a tough time letting this go. I guess I'm going to be super surprised (and disappointed I suppose) if there's no way to use a multi value class type structure for a column value when using a union (or I'm guessing in any query that's computed on the db server) in a way that supports native paging and other such feature niceties.

    I've simplified the query functionality I can't get to work into the query below. If there's any way we can get this type of functionality to work in a way that supports native linq paging (there's gotta be no?!) I'd be very much in your debt.

    var query =
        (from n in db.TableName
         select new DataShapeClass
         {
             Prop = n.Prop,
             ComplexProp = new DataShapeClassInside
             {
                 Prop1 = "",
                
    Prop2 = "",
             }
         })
        .Union(
        from n in db.TableName
         select new DataShapeClass
         {
             Prop = n.Prop,
             ComplexProp = new DataShapeClassInside
             {
                 Prop1 = "",
                
    Prop2 = "",
             }
         });

    Thanks for your help Samir and Matt, I appreciate your time and attention,

    bd

    Saturday, July 19, 2008 10:00 AM
  • What I ended up doing for a workaround was to serialze the data manually, using a comma dillimited string in place of the data shape class column value.

    No doubt there's a perfectly reasonable reason why you can't use classes for column values when using a union, I just don't understand linq2sql well enough to really 'get' it.

    bd

    Tuesday, July 22, 2008 12:53 PM