linq: aggregate into single line (flatten)

c# entity-framework-core flatten linq

Question

I am currently using a linq and accessing some meta data using a for loop:

public SignUpMeta GetSignUpMeta(User user)
{
    var users = (from u in User
                where user.Email == u.Email || user.UserName == u.UserName
                select u);
    var result = new SignUpMeta();
    foreach (var u in users)
    {
        if (user.Email == u.Email) result.IsDuplicateEmail = true;
        if (user.UserName == u.UserName) result.IsDuplicateUserName = true;
    }
    return result;
}

Is there any way for the Linq to generate the SignUpMeta directly? This function is inside the DBContext class and I am looking for a way to get the exact data directly from the db (without raw sql).

Update: The User is DbSet and the whole code runs inside the db context class. I am trying to write something that will make EF fetch the value directly in a single query.

Update 2: The SQL equivalent of what I am looking for would be:

SELECT MAX(username), MAX(email)
(SELECT CAST((UserName = @user) AS bit) username, 
        CAST((Email = @email) AS bit) email
 FROM User WHERE UserName = @user OR Email = @email)

Update 3: SignUpMeta object that needs to be fetched contains the metadata that provides information required for server side validation.

The above C# code runs a query that fetches up to two columns in this instance. When there are more such conditions, there would be more lines. I am trying to find a way that EF would give only the two booleans alone in a single record.

1
0
2/12/2018 12:23:59 AM

Popular Answer

This'll be my try, if you truly must use LINQ:

from u in stuff.Users
group u by 0 into grp
select new
{
    IsDuplicateEmail = grp.Any(x => x.Email == user.Email),
    IsDuplicateUserName = grp.Any(x => x.UserName == user.UserName)
}

Entity Framework will translate that into sub-selects. If you're using SQL Server and have both columns indexed, this should result in the same amount of I/O as your sample SQL.

I don't believe there is any query that will generate your desired sample SQL.

0
2/11/2018 3:49:31 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow