IQueryable to return a single record

IQueryable<T> to return a single record

I am using EF Core and am attempting to create a query provider which accepts any type and a single value. I then want to run a query to return the FirstOrDefault of the passed in items which have the selected property set to the passed in value. something similar to this:

public class TagPicker<T>
{
public IQueryable<T> Pick(IQueryable<T> source, string collumn, string filter)
{
T result = source.FirstOrDefault(r => r.collumn == value);

if (result is null)
{
return new T { collumn = filter };
}
else {
return result;
}

}
}


I have several types which will need this type of query performed. I am trying to avoid having to create duplicates of this type of query for each type.
Any Ideas?

What problem are you trying to solve with this code? This looks like an attempt to replicate Where() or FirstOrDefault(predicate), except it doesn't work. You can't create queries that can't be mapped to SQL in the first place. The where clause in the query compares two string instead of a column and a value.
– Panagiotis Kanavos
Jun 29 at 10:22

Where()

FirstOrDefault(predicate)

where

Not sure why you need this, can't you just use the predicate filter that FirstOrDefault has? For example source.FirstOrDefault(x => x.Value = 4)
– DavidG
Jun 29 at 10:23

FirstOrDefault

source.FirstOrDefault(x => x.Value = 4)

BTW both Where() and FirstOrDefault accept any type and any predicate - expression actually. You can store the lambda expression in a variable and pass it to Where() or FirstOrDefaultAsync, so there's no reason to try and construct your own FirstOrDefaultAsync
– Panagiotis Kanavos
Jun 29 at 10:23

Where()

FirstOrDefault

Where()

FirstOrDefaultAsync

FirstOrDefaultAsync

If you want to return a IQueryable<> (not sure if it is an intelligent thing), there is the .Take(1) to return 0...1 elements.
– xanatos
Jun 29 at 10:30

IQueryable<>

.Take(1)

@xanatos If I tried to guess the actual purpose of this code though, I'd say it tries to do two things - find a matching tag and when that fails, create a new one. One of these things is a SQL query. The other is post-processing of the query results. Why column though? That is a third thing - multiple columns that map to the same entity type. That's something that should be handled by mapping either during DbContext configuration or with attributes.
– Panagiotis Kanavos
Jun 29 at 11:08

2 Answers
2

What you are trying to do is probably wrong... As Panagiotis wrote you could pass a lambda expression to the Pick method. But I'll try to make your code working. You probably have a XY problem. The right solution would be to comprehend what is the Y problem and what is the Y solution. I will instead give the solution of the X problem.

Pick

Now, what you are trying to do (putting a column name in a string) is called Dynamic Linq. We (where "we" are a big group of C# programmers) try to not use it, because it isn't very "safe", because keeping name of columns inside of strings make the code difficult to refactor... But still there is a niche of situations where this is necessary. There are various libraries in .NET to do Dynamic Linq. The one that is currently being developed is System.Linq.Dynamic.Core. It has a very practical
nuget.

string

string

Example of code:

using System.Linq.Dynamic.Core;

public static class TagPicker
{
public static T Pick<T>(this IQueryable<T> source, string column, string filter) where T : class, new()
{
// Dynamic Linq supports query in the form *columnname = @0*,
// where @0 is the first parameter (and @1 the second and so on)
T result = source.FirstOrDefault(column + " = @0", filter);

if (result is null)
{
// We use reflection to find the column *column* and
// set its value to *filter*. Note that we don't try
// to do a cast, so *column* must be of type *string*
result = new T();
typeof(T).GetProperty(column).SetValue(result, filter);
}

return result;
}
}


and then use it like:

using (var context = new MyDbContext())
{
var result = context.Products.Pick("ProductName", "Foo");
}


Now... Out of curiousity, what probably Panagiotis proposed:

public static T Pick<T>(this IQueryable<T> source, Expression<Func<T, string>> column, string filter) where T : class, new()
{
Expression<Func<T, bool>> columnFilter = Expression.Lambda<Func<T, bool>>(Expression.Equal(column.Body, Expression.Constant(filter)), column.Parameters);

T result = source.FirstOrDefault(columnFilter);

if (result is null)
{
result = new T();
Expression<Action<T>> assign = Expression.Lambda<Action<T>>(Expression.Assign(column.Body, Expression.Constant(filter)), column.Parameters);

// If you can't compile with the true because you are using an old .NET, remove it
Action<T> assignCompiled = assign.Compile(true);
assignCompiled(result);
}

return result;
}


Use it like:

var result = context.Products.Pick(x => x.ProductName, "Foo2");


Note that now x.ProductName isn't anymore a string, it is a lambda expression, so it is validated by the compiler. You could have:

x.ProductName

string

Expression<Func<Product, string>> selector;

if (somecondition)
{
selector = x => x.ProductName;
}
else
{
selector = x => x.UnitName;
}

var result2 = context.Products.Pick(selector, "Foo2");


or in general save the selectors in some variable/select the right selector in any way.

While this does work. I like the simplicity of Panagiotis Kanavos comment. whateverTaggedContext.FirstOrDefault(it=>it.Tag==value) ?? new Whatever(value) I know his response was probably obvious, but hey... I'm new at this.
– user3779856
Jun 29 at 21:29

@PanagiotisKanavos commented a simple solution for doing this. Here it is slightly modified:

Object.Foo = context.Foos.FirstOrDefault(x => x.Property == form.Value) ?? new Foo { Property = form.Value };


Hope this helps someone

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.