CODEWITHSUNDEEP
c#sqllinq
mmekaiel
mmekaiel

Reputation: 91

"Subquery returned more than 1 value. This is not permitted." Need to return a set of values

var range = this.dataStore.Query<NotificationConfiguration>()
                .Range(p => new NotificationConfigurationViewModel(p, from x in p.Events where !(x.Code == null || x.Code.Equals("")) select x.Code), pageNumber);

return this.View(range);

I have the above code in which I want to return an IEnumerable but get a "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression" every time the code is run. I understand that the query wants to return just one value, but how do I get it to return a set of values as intended? Help please!

Let me clarify a little... I want the range to include a new object. I have a query which is going through the database. It returns the data which I then convert using the following view model constructor:

public NotificationConfigurationViewModel(NotificationConfiguration notification , IEnumerable<string> codes)
{
    Contract.Requires(notification != null);

    this.notification = notification;
    this.codes = codes;
}

Each Notification Configuration has properties and then a list of events tied to it. I need just the codes from said list.

Just to clarify again. I want the query to give me back a NotificationConfiguration and also an IEnumerable (which I will later transform to a single string using SB). Once the query gives me back those two items I will transform it using a constructor in my view model so I can properly display all the data using a DataTable. The answer I am looking for maybe very specific but I need to understand why I'm getting the subquery error when I want it to return an IEnumerable and how to fix it. Also please note... according to the .net docs the code should be handing my back an IEnumerable but for some reason its still crashing. Here is the relevant code samples again:

        [HttpPost]
    public ActionResult Index(DataTableRequest requestedData)
    {
        using (this.dataStore.Session.BeginTransaction())
        {
            return this.dataStore.Query<NotificationConfiguration>()
                          .TableRange(requestedData, p => new NotificationConfigurationViewModel(p, from x in p.Events select x.Code));
        }
    }

.

        public NotificationConfigurationViewModel(NotificationConfiguration notification , IEnumerable<string> events)
    {
        Contract.Requires(notification != null);

        this.notification = notification;
        this.events = events;
    }

.

    [Display(Name = "Events")]
    public virtual string EventTypeCodes
    {
        get
        {
            var codes = new StringBuilder();

            foreach (var item in this.events)
            {
               codes.Append(item + ",");
            }

            return codes.ToString().TrimEnd(',');
        }
    }

Upvotes: 1

Views: 571

Answers (3)

Ryan Gates
Ryan Gates

Reputation: 4539

The issue here is with Range method (copied below).

.Range(p => new NotificationConfigurationViewModel(p, from x in p.Events where !(x.Code == null || x.Code.Equals("")) select x.Code), pageNumber);

The Range expects the caller to pass in the following arguments:

public static IEnumerable<int> Range(
    int start,
    int count
)

The code is passing in (NotificationConfigurationViewModel, int). This certainly seems to be part of the issue. I believe that the appropriate solution would be the following:

var range = from p in this.dataStore.Query<NotificationConfiguration>()
select new NotificationConfigurationViewModel(p, p.Events.Where(x => !string.IsNullOrEmpty(x.Code)));

This will convert from your NotificationConfiguration to NotificationConfigurationViewModel while not including any codes that are empty strings.

Upvotes: 2

TylerOhlsen
TylerOhlsen

Reputation: 5578

Going on assumptions... is this what you are after?

If you are trying to create a bunch of new objects, the object creation is best done inside the select part of the query.

int pageNumber = 0;
int pageSize = 10;

IQueryable<NotificationConfiguration> configurations = this.dataStore.Query<NotificationConfiguration>();

IList<NotificationConfigurationViewModel> viewModels =
    (from configuration in configurations
     where !string.IsNullOrEmpty(configuration.Code)
     select new NotificationConfigurationViewModel(configuration))
    .Skip(pageNumber * pageSize).Take(pageSize).ToList();

return viewModels;

Upvotes: 0

Ian P
Ian P

Reputation: 1724

Well the answer is in the query, not the data set, so to give an exact answer we need to see the query. However in general there are a few constructs you can use. You probably have something like WHERE Id = (SELECT ID FROM Values WHERE DateOFValues BETWEEN FirstDate AND LastDate). WHAT YOU Want is WHERE ID IN (SELEct... OR WHERE IS = ANY (SELECT... or a sub query in which case look here... http://allenbrowne.com/subquery-01.html (yes I know it says access, but MS SQL supports all the sytax used here.

Upvotes: 0

Related Questions