1

I am working on an application that accepts a document as an upload, an excel sheet with multiple values. It is breaking and not uploading... I have located where it is breaking but I am having problems understanding this exact LINQ syntax.

    public static List<PoolManager> getPoolManagersLoadedinMaster()
    {
        using (HRCMSEntities context = new HRCMSEntities())
        {

            HRCompInfo compInfo = new HRCompInfo();
            DataTable dtCurrYear = compInfo.getConfiguration("CurrentYear");
            var currYear = Convert.ToInt32(dtCurrYear.Rows[0]["Config_Val"].ToString());

            var planningManagerQuery =
            from COMP_REC_ENC in context.COMP_REC_ENC.Where(m => m.Year == currYear)
            select new PoolManager
            {
                Planning_manager_ID = COMP_REC_ENC.Planning_Manager_ID
            };

            var planningManagerList = planningManagerQuery.Distinct().ToList();

            return planningManagerList;
        }
    }

This code is breaking when it tries to run

PlanningMangerList = planningManagerQuery.Distinct().ToList() 

I checked the table and the data is there. Can someone help me decipher why this code is breaking on that exact part? Theories would work just fine... I am getting this error:

ERROR: System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Invalid column name 'Special_6'. Invalid column name 'Special_9_New'. Invalid column name 'Special_10_New'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior

I did reciently change this project from a SIT database to a DEV database... When it comes to entity framework I did update the connection string catalog but am unsure if the connectionString is a problem when it comes to entity framekwork. I updated it to this.

<add name="HRCMSEntities" connectionString="metadata=res://*/;provider=System.Data.SqlClient

Thanks

10
  • Can you please add a catch clause and show us the exception.ToString() output? Commented Aug 13, 2020 at 5:49
  • Yes I am doing it now... one moment Commented Aug 13, 2020 at 5:50
  • @noobprogrammer - Take another look. Commented Aug 13, 2020 at 5:51
  • @JeffreyPadgett - Can we please see the class definition for PoolManager? Commented Aug 13, 2020 at 5:52
  • 2
    How about you enable logging of sql queries and then try to execute the query manually to see what's the issue Commented Aug 13, 2020 at 6:07

1 Answer 1

1

The cause of your error has nothing to do with Distinct(), nor with ToList(). The error is reported here, because this is the moment that the query that you defined is executed.

I think the problem is in the Select, this looks a bit weird to me.

You have to debug to find out if the problem is in the dbContext, the Where or the Select.

Add some intermediate test code, every time execute the query.

var a = context.COMP_REC_ENC.ToList();
var b = context.COMP_REC_ENC
        .Where(m => m.Year == currYear)
        .ToList();
var c = context.COMP_REC_ENC
        .Where(m => m.Year == currYear)
        .Select(m => new PoolManager
        {
            Planning_manager_ID = COMP_REC_ENC.Planning_Manager_ID
        })
        .ToList();

In words: the context has a sequence of items with the cryptical name COMP_REC_ENC. Every item in this sequence has a property Year. Keep only those COMP_REC_ENC from the sequence that have a value for this property equal to currYear

First of all: currYear is an int. Is property Year also an int? If so, the problem is not in the Where.

Next, from every item in the remaining sequence of COMP_REC_ENC objects, make one new PoolManager. Only property Planning_manager_ID is filled. The value used to fill Planning_manager_ID is COMP_REC_ENC.Planning_Manager_ID

Here comes the strange part: I had expected that you would use one of the properties of the current COMP_REC_ENC to fill the Planning_Manager_ID. I think you will find the problem here.

Planning_manager_ID = 

How to prevent this kind of problems in future

I have several advices for you:

  • Don't mix LINQ method syntax with LINQ query syntax, people coming from SQL world like the query syntax more. People coming from C# world probably prefer method syntax. Method syntax is more flexible, because it is easy to add new LINQ-like methods.
  • Try to give your identifiers meaningful names, so every reader will know immediately what they stand for. Use plural nouns for sequences of similar items, use singular nouns for elements from these sequences.
  • Don't think that saving on typing by using short identifiers will finish your project earlier. The saved time is nothing compared to the time needed for others to understand the code when they have to change / test / reuse it.

If the dbContext class is already used widely, I can understand that it will be hard to convince your project leader to change the dbContext. So you'll have to stick with that.

I haven't got a clue what items are stored in your dbSet COMP_REC_ENC, so I'll just give an example to show how much clearer your query will be if you stick to these rules

class ComputerPlanning
{
    public int Id {get; set; }                 // primary key
    ...

    public int Year {get; set;}   
    public int PlanningManagerId {get; set;}   // The foreign key to the planning manager
                                               // who created this planning
}

class PlanningDbContext : DbContext
{
    public DbSet<ComputerPlanning> ComputerPlannings {get; set;}
}
    

Your query according to the guidelines:

Requirement: from all computer plannings in the table of ComputerPlannings, keep only those computer plannings that have a Year equal to currentYear. From every remaining ComputerPlanning make one PoolManager with a value of PlanningManagerId equal to the computer planning PlanningManagerId. Remove duplicates and put it all in a list.

int currentYear = ...
List<PoolManager> poolManagers = planningContext.ComputerPlannings
    .Where(computerPlanning => computerPlanning.Year == currentYear)
    .Select(computerPlanning => new PoolManager
    {
         PlanningManagerId = computerPlanning.PlanningManagerId,
    })
    .Distinct()
    .ToList();

Because I stuck to the conventions, it is easy to see for everyone whoe reads the code to see that the code does what the requirement says.

By the way, if you had made an error in Planning_manager_ID = ..., your compiler would probably have found the problem already, instead of run time.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.