Why does LINQ Query return more results/rows than the table I am querying?

For the sake of context, I am working on a 1 time data conversion from several Excel sheets that I have imported into a Database. The data is not normalized and I am trying to normalize it.

I have 3 tables in a SQL Database. They are called OldAssets, OldTransactions, and OldUsers.

OldTransactions has 7903 records. OldUsers has 7437. OldAssets has 9764

I am using LINQ to SQL to query these tables using this code

from oa in OldAssets
from ot in OldTransactions
from u in OldUsers
where (oa.Asset_Serial_Number == ot.Asset_Serial_Number  && ot.User_EID == u.User_EID  && ot.Asset_Tag == oa.Asset_Tag)
select new Transactions { 
            DevCenter = ot.Transaction_Dev_Center,
            Action  =   ot.Transaction_Action, 
            Status  =   ot.Transaction_Status,
            ModificationDate =  ot.Modified,
            ModifiedBy = ot.ModifiedBy,
            CreatedBy = ot.CreatedBy,
            TransactionDate = (System.DateTime)ot.Transaction_Date,
            Transaction_Asset = (System.Int32)oa.ID,
            Transaction_User = (System.Int32)u.ID }

I am trying to go through all of my OldTransactions and for each OldTransaction, create a new transaction that specifies the Transaction_Asset and the Transaction_User based on the mappin开发者_JS百科g of an asset serial number and a user_EID.

My result is giving me just over 10,000 records of new "Transactions." I don't understand how I could be getting more records than the number of OldTransactions I have.

What query can I write that will return a single new Transaction for every OldTransaction, but with the added properties of Transaction_Asset and Transaction_User based on the mapping of serial_number and User_EID?

Why shouldn't it? You're selecting from the cartesian product of the three tables, meaning that the total number of rows is 7903 x 7437 x 9764, which is several billion: all possible combinations of a row from OldAssets, a row from OldTransactions and a row from OldUsers.

Then these several billion are filtered by the where condition, and you are left with just over 10,000, as you said.

From you question it looks like you just want to copy all transactions with additional data. I think something like this could work:

from ot in OldTransactions
select new Transactions {
        DevCenter = ot.Transaction_Dev_Center,
        Action  =   ot.Transaction_Action,
        Status  =   ot.Transaction_Status,
        ModificationDate =  ot.Modified,
        ModifiedBy = ot.ModifiedBy,
        CreatedBy = ot.CreatedBy,
        TransactionDate = (System.DateTime)ot.Transaction_Date,
        Transaction_Asset = (System.Int32)(
             from oa in OldAssets 
             where oa.Asset_Serial_Number == ot.Asset_Serial_Number && 
                   ot.Asset_Tag == oa.Asset_Tag
             select oa.ID).FirstOrDefault(),
        Transaction_User = (System.Int32)(
             from u in OldUsers 
             where ot.User_EID == u.User_EID
             select u.ID).
                 FirstOrDefault()) }