Entity Framework Include or Select based on Column Value

2022-01-18 03:22 Tjopsta imported from Stackoverflow

I'd like to do something like a conditional include or select based on the value of a column in my parent table.

Parent table:

Guid Id  
nvarchar(50) Name  
int Type  
Guid ObjectId

Child 1

Guid Id  
nvarchar(50) Name

Child 2

Guid Id  
nvarchar(50) Name

When I populate my model, I need to do a condition based on what type id is in the parent table.

For example

If the row Type == 1, then the include / select needs to be done on Child 1 for Id and Name.
If the type == 2 then from Child 2.

You would of course not be able to do a normal include, so would a select be the better option here? How would I go about it?

See also questions close to this topic

C# - Adding condition to func results in stack overflow exception
I have a func as part of specification class which sorts the given iqueryable
```
Func<IQueryable<T>, IOrderedQueryable<T>>? Sort { get; set; }
```
When i add more than one condition to the func like below , it results in stack overflow exception.
```
spec.OrderBy(sc => sc.Case.EndTime).OrderBy(sc => sc.Case.StartTime);
```
The OrderBy method is implemented like this
```
public ISpecification<T> OrderBy<TProperty>(Expression<Func<T, TProperty>> property)
    {
        _ = Sort == null ? Sort = items => items.OrderBy(property) : Sort = items => Sort(items).ThenBy(property);
        return this;
    }
```
Chaining or using separate lines doesn't make a difference.

This problem gets resolved if I assign a new instance of the specification and set it's func, but i don't want to be assigning to a new instance everytime. Please suggest what am i missing here and how to reuse the same instance (if possible).

How to projection fields for a dictionary (C#, MongdoDB)

I am trying my luck here, I have a model which is like the following

public class RowData : BaseBsonDefinition
{
.
  [BsonExtraElements]
  [BsonDictionaryOptions(DictionaryRepresentation.ArrayOfDocuments)]
  public Dictionary<string, object> Rows { get; set; } = new(StringComparer.OrdinalIgnoreCase);
.
}

In result, the schema in the MongoDB looks like

{
"_id": {
    "$binary": {
      "base64": "HiuI1sgyT0OZmcgGUit2dw==",
      "subType": "03"
    }
  },
  "c1": "AAA",
  "c8": "Fully Vac",
  "c10": "",
}

Those c1, c8 and c10 fields are keys from the dictionary, my question is how to dynamic project those fields?

I tried

Builders<RowData>.Projection.Exclude(p => "c1")

It seems the MongoDB driver can not handle a value directly.

Anyone could point me in the correct direction?

Thanks,

How do I add new DataSource to an already Databinded CheckBoxList

i'm building a web form that show Database's item(Tables, Rows, FK,...)

I have a CheckBoxList of Tables (chkListTable) which will show a new CheckBoxList of Rows (chkListRow) everytime I SelectedIndexChanged from chkListTable. The problem is i can show the items from chkListTable with 1 selected item. But i don't know how to show chkListRow if multiple item from chkListTable are selected.

Here are my codes:

aspx:

<div>
             <asp:Label ID="Label2" runat="server" Text="Table: "></asp:Label>
             <asp:CheckBoxList ID="chkListTable" runat="server"
                 DataTextField="name"
                 DataValueFeild="name"
                 AutoPostBack="true"
                 OnSelectedIndexChanged="chkListTable_SelectedIndexChanged">
             </asp:CheckBoxList>
        </div>
        <div>
             <asp:CheckBoxList ID="chkListRow" runat="server"
             DataTextField="COLUMN_NAME"
             DataValueField="COLUMN_NAME"
             RepeatDirection="Horizontal">
             </asp:CheckBoxList>
        </div>

aspx.cs:

protected void chkListTable_SelectedIndexChanged(object sender, EventArgs e)
    {
        tableName.Clear();
        
        foreach (ListItem item in chkListTable.Items)
        {
            if(item.Selected)
            {
                tableName.Add(item.Text.Trim());
            }    
        }

        for(int i = 0; i < tableName.Count; i++)
        {
            String query = "USE " + dbname +
                " SELECT * FROM information_schema.columns" +
                " WHERE table_name = '" + tableName[i] + "'" +
                " AND COLUMN_NAME != 'rowguid'";
            chkListRow.DataSource = Program.ExecSqlDataReader(query);
            chkListRow.DataBind(); 
            Program.conn.Close();
        }
    }

Program.cs:

public static bool Connect()
    {
        if (Program.conn != null && Program.conn.State == ConnectionState.Open)
            Program.conn.Close();
        try
        {
            Program.conn.ConnectionString = Program.constr;

            Program.conn.Open();

            return true;
        }

        catch (Exception e)
        {
            return false;
        }
    }

    public static SqlDataReader ExecSqlDataReader(String query)
    {
        SqlDataReader myreader;
        SqlCommand sqlcmd = new SqlCommand(query, Program.conn);
        sqlcmd.CommandType = CommandType.Text;
        if (Program.conn.State == ConnectionState.Closed) Program.conn.Open();
        try
        {
            myreader = sqlcmd.ExecuteReader();
            return myreader;
            myreader.Close();
        }
        catch (SqlException ex)
        {
            Program.conn.Close();
            return null;
        }
    }

I want my display to be like this:

[x]Table1 [x]Table2 [ ]Table3
[ ]Row1(Table1) [ ]Row2(Table1) [ ]Row3(Table1)
[ ]Row1(Table2) [ ]Row2(Table2)

site only opens in CefSharp

zaakr.net is a site can be opened only in zaakr.exe application or through android(.apk), when opened in browser u been redirected to https://browser.zaakr.net so u download there application, (.exe) file is using cefsharp as a browser so it can access website, from 2 month i could open it from chromium as cefsharp uses chromium, now i can't access website through chromium for some reason idk, i even made a cefsharp browser myself using visual studio nothing worked all the time i been redirected to browser.zaakr.com, i want to access through any browser that have devtools available for a project, note that application zaakr.exe is still using cefsharp even the older version still can access site. i would appreciate any help, sry for any spelling mistakes, thanks.
C# MariaDB Update Function working in DataStore but not updating in .NET Application
My initial issue is that my database does not update when I call on this specific code. I am not sure if it is C# itself or the update query that I am calling.
```
string _connectionString = "validConnectionstring";

using (MySqlConnection _mySqlConnection = new MySqlConnection(_connectionString)
{
_mySqlConnection.Open();
using (MySqlCommand command = new MySqlCommand("UpdateProfileStatus", _mySqlConnection))
{
    command.Transaction = _mySqlConnection.BeginTransaction();
    command.CommandTimeout = TimeSpan.FromSeconds(60).Seconds;
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@_username", "test");
    command.Parameters.AddWithValue("@_status", true);
    command.ExecuteNonQuery();
    
}
_mySqlConnection.Close();
}
```
I get no updates in my database but my console logs that the query is executed returning the value of 1 but there is no update that actually happens in my DB. Is there something in my code to reason why it is failing?

Here is the stored procedure that I have for the update command
```
CREATE PROCEDURE UpdateProfileStatus(_username VARCHAR(25), _status BOOL)
UPDATE Profile SET status = _status WHERE username = _username;
```
I know the Stored Procedure works but am not sure why my .NET application is not responding to my procedure call. Is it something to do with my implementation of the parameters or is it my procedure itself?

Image of DataStore Setting within MariaDB
How to add a new Model visually in Mac OS visual Studio
I do not see any choice to create a model in Mac visual studio? I In the windows version the Class created with its imports
- using Linq
  
  using Threading.Tasks
  
  using Collection.Generic
C# ef code first How to have a one-to-one relationship with

How to make a one-to-one relationship with code first in c# desktop application, can you help
LINQ select column that contains some array element
I have a field "details" varchar(MAX) in my table with the detailed description of an item, I need to select the records that contain the keywords passed by the user in a string array.
```
var searchTerms = new List<string> { "car", "black", "bike", "blue" };
```
I tried to do it like this:
```
var result = (from i in _contexto.itens
where searchTerms.Any(d => i.details.Contains(d))
select i.id);
```
and I get the following error:

The LINQ expression 'DbSet() .Where(i => __searchTerms_0 .Any(d => i.details.Contains(d)))' 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.

I also tried this link's approach using the "SearchExtensions nuget package" and the same error occurs.

How can I do this? or should I make a query for each item in the array?

Example of a text I want to search for:

It is a long established fact that a car will be distracted by the readable content of a page when looking at its bike. The point of using Lorem Ipsum is that it has a more-or-less normal distribution

Thanks!

How to use a database connection string in App.config?

I use EF 6, and if I use a hardcode database connection in the OnConfiguring:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Data Source=.\\sqlexpress;Initial Catalog=MyOrders;Persist Security Info=True;User ID=sa;Password=***4n**user");
            }
        }

the code will work well.

but if I use a connect string in App.config:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(ConfigurationManager.AppSettings["DataBase"].ToString().Trim());                
        }
    }

it will not work.

the connect string in App.config:

    <add key="DataBase" value="Data Source=.\\sqlexpress;Initial Catalog=MyOrders;Persist Security Info=True;User ID=sa;Password=pk94newuser"/>

the error message:

Instance failure.

the StrackTrace:

at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)

I want to know how to use a connect string in EF 6 correctly. Thanks!

Related questions

Entity Framework Include or Select based on Column Value

See also questions close to this topic