Home > Software design >  Trouble with Linq Select Query
Trouble with Linq Select Query

Time:03-26

I am trying to query my Database for a single record ins three steps and I am having problems getting the result that I am looking for. These are the steps that I created:

                client = client
                    .Where(s => s.CompanyName.Contains(name));

                var res = client.Select(x => x.ID);

                Tracker = Tracker
                    .Where(s => s.ClientId.Equals(client.Select(x => x.ID)));

Debugging the code indicated that steps one and two worked correctly and generated the data that I needed to run my third query, which should provide the whole record, utilizing the result of the second step.

The third and last steps generated the following error:

"The LINQ expression 'DbSet<TimeTrackerViewModel>()\r\n    .Where(t => t.ClientId.Equals(DbSet<ClientsViewModel>()\r\n        .Where(c => c.CompanyName.Contains(__name_0))\r\n        .Select(c => c.ID)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."

How do I query my database, utilizing the query result from the second step?

CodePudding user response:

If you want to use the IDs in res to filter Tracker then you can use Any():

var res = client.Select(x => x.ID);
trackers = Tracker.Where(s => res.Any(r => r == s.ClientId));

The above query will return a collection.

I am trying to query my Database for a single record

If you want to return a single record then you could to use FirstOrDefault(), either in place of the Where clause (using the same predicate), or after the Where (you could consider Single() if you know there's exactly 1 matching record). But you should also consider what you expect to happen if multiple records match the name parameter in your first query and how you would handle that.

CodePudding user response:

After reading several related posts, I was able to combine thier ideas into a single working solution as posted below:

                var client = _context.ClientsViewModel
                    .Where(s => s.CompanyName.Contains(name))
                    .Select(x => x.ID).ToList();

                Tracker = Tracker
                    .Where(s => s.ClientId == client[0])
                    .OrderByDescending(x => x.Id);
  • Related